Sysadmin Lockout

I ran across this issue today and thought I'd throw a quick post together.

On a particular SQL Server 2008 instance, all of the windows accounts/groups defined as SQL Server sysadmins had been removed, and the server was operating in windows integrated security mode. In SQL Server 2008, the BUILTIN\Administrators group is no longer created by the installation process, and in earlier versions it was best practice to remove it.

In my particular case, no one could connect to the server as sysadmin. After a bit of excitement and crazy talk about reinstalling SQL Server and reattaching databases, sense prevailed, and we started SQL Server in single user mode using the "-m" startup option. After starting SQL Server in this mode you can connect as a SQL sysadmin if you're a member of the server's local administrators group (even if you're not defined with a SQL Server sysadmin login).

One thing to be careful of here is SQL Server Agent will grab the single user connection if it's set to auto-start, so you'd need to shut down the agent service to get the single connection.

The moral of the story here is that you can't prevent Windows administrators from being SQL Server sysadmins .... damn.