Blog Post

Help I'm not Sysadmin!

,

I am currently working on some client servers to upgrade multiple SQL 2008 R2 Express instances to 2008 R2 Standard.  On several of them, the only sysadmin is sa, and we don’t have the password.  BUILTIN\Administrators is only in dbcreator by the configuration in place, even though by the client standard it should be sysadmin.
 
To correct this I had to:
Stop the service:
net stop MSSQL$EDIENTERPRISE
Start the service in single-user mode (/m):
net start MSSQL$EDIENTERPRISE /m
Connect a query window to the server and run the following query to add BUILTIN\Administrators to sysadmin:
sp_addsrvrolemember 'BUILTIN\Administrators','sysadmin'
then restart MSSQL$EDIENTERPRISE w/o any flags to return SQL to regular multi-user mode
net stop MSSQL$EDIENTERPRISE
net start MSSQL$EDIENTERPRISE
--
Since 2005 (and at least through 2008 R2  - dunno about 2012) when SQL Server (any SQL Server, not just Express) is started in single-user mode, a Windows Admins who connects is a sysadmin even if BUILTIN\Administrators aren't actually a member of the sysadmin group - this allowed me to connect in single user mode and add the Admin group to sysadmins for real.
This can save your bacon (as in this case) or be a real security hole (if you have purposely tried to lock your Windows Admins out) - either way be aware!

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating