Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Nebraska SQL from @DBA_ANDY

I’m a forty-something Microsoft SQL Server DBA of 15+ years, a devoted husband, and a father of three young boys. I have been a DBA at a public university, at a major bank, at a healthcare system, and I now work as a remote DBA with customers across the United States. I write and speak primarily about the tips and tricks that I discover along my SQL Server journey.

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:


Start the service in single-user mode (/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



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!


Leave a comment on the original post [, opens in a new window]

Loading comments...