Renaming the sa Account
Today I renamed the sa account on 23 SQL Server 2005/2008 instances. I used the CMS to assist with this task.
Later we realized all of the SQL Agent jobs were failing on these instances with the following error: “The job failed. The owner (sa) of job XYZ does not have server access.”
We use sa as the job owner, and it had correctly changed the owner to our new name as the sids had not changed. We were at first confused why the jobs were failing but then realized restarting the SQL Agent service might help. The restart corrected the problem.
If you plan on renaming your sa account (best practice), make sure you restart the SQL Agent service afterwards to avoid failing jobs. This is perhaps common knowledge, but it was something new learned by me today.
Legacy Comments
|
Mark
2011-08-25 |
re: Renaming the sa Account Great post as always Tara. Something to keep on the radar with instances where sa has been renamed is the issue described in KB 968829. The workarounds are straightforward (upgrade directly to slipstreamed SQL 2008 SP1 or replace sa), but it can be a curveball in the middle of an upgrade. |
|
Saurabh
2011-10-20 |
re: Renaming the sa Account I would also recommend keep an eye while applying any SQL patch or doing an SQL upgrade. As the sql scripts use sa account and your patch or upgrade will fail. During that time, you might want to change your accounts to sa and then revert it back after the upgrade. I have learnt that hard way! |