Changing your SA Account

Mon Nov 24, 2008 by Chris Miller

I just went through an exercise to change the name of my SA login. I renamed it to something confidential, set it with a strong password, and then created a new login called 'sa' with the same password as the old SA.

"Hey, why don't you just disable it?"

Well, seems that the ISV that is using this particular server hardcoded SA into their application. If you're a manager, this is why you get a DBA involved in acquisition of these kinds of systems. Believe it or not, that's not the dumbest thing this ISV's done...

"Well, why don't you just put a strong password on SA then?"

Two reasons. First, SA is an obvious entry point into the system, and I'd rather keep any potential hackers having to guess at the supreme admin account on my server.

Second reason, and the dumbest thing I've ever seen in any software: They not only hardcoded their application to use SA, they hardcoded it to use a particular password, 4 characters, lowercase, and it's the first 4 letters of their company name.

Did I mention this ISV is a big bunch of morons? I bet they wear slip on shoes because they can't figure out knots.

Yeah.

So here are some intriguing gotchas.

First, SA probably owns most of your SQL Agent jobs. And if you rename it, the job's owner ID does not change, HOWEVER, the SQLAgent caches the login *name* not the login *SID*. So you either need to (1) restart the SQLAgent service, or (2):

use msdb

select 'exec sp_sqlagent_notify @op_type = N''J'', @job_id = '''

+ convert(varchar(50), job_id) + ''', @action_type = N''U'''

from sysjobs

And then run the script that pops out of that. It tells the SQL Agent that the jobs changed materially, and the SQLAgent does a rescan of sysjobs to get it's story straight. Yes, you could do the *exact* same thing with a cursor. But cursors suck, and I can avoid them here with a quick copy/paste, and I'm only doing this once, so...deal with it. If you choose to rename *and* disable the SA account, you'll want to go look up the sp_update_job stored procedure for a quick way to change all your job owners over to a new account.

Finally, there's the issue of default object usage. Since, oh, the entire reporting infrastructure of this system runs as SA (bonus credit problem: what's the implication of this if a report running as SA deadlocks with an accountant entering transactions on their user account?) and the reporting system uses stored procedures to return data, there were no permissions set on any of the reporting stored procedures, so I had to clean that up.

The only reason I did this is that we allow the ISV to have access to the server since it's in production-parallel right now so they can troubleshoot and diagnose problems (frequent) and every now and then, despite us telling them over and over not to log in as SA, change objects, or do backups, they frequently log in as SA, change objects without telling us, and do backups which jack up my ability to recover. So their lack of respect is what got them into this anyway.