Shawn Meyers (@1dizzygoose), Principal Architect

I was at SQL Saturday #397 in Omaha earlier this month and sat in on an excellent session on “SQL Server Performance and Resiliency” by Chris Shaw. In his session, Chris talked about the SA account and not giving it out. I shared a tip with the group during his session and Chris stated it was a brilliant idea and that I needed to blog about it, so I pulled a post together and here it is.

We all know that we should never give any account SA rights unless absolutely needed, and that no application should ever use the actual SA account to login. But, every once in a while we run into a situation where the vendor demands to use the SA account, or have even hard coded the application to use the SA account. After a long fight, someone higher up finds out that they paid for this application and we have to give them SA to make it work and we (the DBAs) are told to just live with it.

I have a solution for this, which I have used in a few places. I rename the SA account to something different and create a new account called SA, and give it DBO to the vendor database. Then I go back to dealing with real issues instead of trying to teach a vendor how to do their job, or explaining SQL server security.

When you rename the SA account, all databases which are owned by SA will still be owned by the new account. In my tests for this post, I used the account oldSA. When creating new databases, you will need to make sure you specify the new SA account, which I called oldSA (don’t question my logic, I can’t answer it myself).

Notes about running with a changed SA account

A common best practice is that all databases are owned by SA, and many scripts for monitoring will flag a database when not owned by SA. Since you changed the SA account, you will also need to update your monitoring processes.

However, the change database owner scripts will work still work the same.

EXEC sp_changedbowner 'oldSA'

ALTER AUTHORIZATION ON DATABASE:: SQLInventory TO [oldSA]

What happens when I need to login as SA?

Nothing, just make sure you have documented what you called the account, because if you log in as SA you’ll have the restricted account.

How do I know what the SA account is, in case I forgot what I set it to?

First make sure you document it, since you shouldn’t change settings in your servers without documentation. But you might inherit a server someone else did this with, so I describe how to find the SA account below.

Any member of the public role can expand the security tab and see the SA account, or in this case the old SA account.

Note: We recommend that you create a more anonymously titled user name (i.e. John Hancock, John Doe, etc.) so it’s not as obvious of an account to attempt a brute force attack against.

It is also logged in the default trace, but it takes a while to dig for it.

What happens when I try to access a server where no one has credentials?

In the case of a server with no permissions, a common method is to change the server to single use mode, which allows any administrator on the local server to access SQL server in single user mode. You can either change the SA password here or add a known account as a sysadmin, but it all works the same if you haven’t changed the SA account name.

Using the –m method for the startup parameter, I started SQL server in single user mode.

First tried to connect as the SA account, but it failed as the account I created called SA isn’t an administrator.

I have done this a few times and I have yet to run into any issues. I tried to think through all the possible issues and I have not uncovered any so far. If you have any questions or know of an issue this presents please contact me or leave a comment below.