Batch File to Grant Local Administrators a Sysadmin Login in SQL Server


In SQL Server members of the local administrators group are often configured to log in as sysadmins.  A sysadmin, of course, has complete control of the SQL Server instance.  Some people remove the local administrators login from SQL Server to prevent access by non-DBA Windows admins.  This is a reasonable configuration, but it doesn’t prevent a Windows admin from taking control of SQL Server if necessary.

The procedure for a Windows admin to take control of a SQL instance is documented here:

Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out

Anyway for convenience, here’s a batch file that will do it for you.

admin2sysadmin.bat

net stop mssqlserver 
net start mssqlserver /mSQLCMD 
sqlcmd -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'" 
net stop mssqlserver 
net start mssqlserver 
sqlcmd -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

This one is for a default instance.  For a named instance (say sqlexpress) it would look like:

net stop mssql$sqlexpress 
net start mssql$sqlexpress /mSQLCMD 
sqlcmd -S (local)\sqlexpress -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'" 
net stop mssql$sqlexpress
net start mssql$sqlexpress
sqlcmd -S (local)\sqlexpress -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

David

dbrowne_at_microsoft


Comments (12)

  1. Chris Towles says:

    Great Post, Really useful.

    http://www.christowles.com

  2. Shubho says:

    You saved my day man, many thanks!

  3. Tom Le says:

    Excellent. it worked well for me. very helpful

  4. Bill says:

    Great! Thanks

  5. Thomas says:

    Hi it worked.

    Saved me from problems while our normal DBA was on holliday.

  6. Daniel says:

    Great stuff !

  7. Tony says:

    Do you have a version that would work for a SQL cluster?  I tried this script, and SQL Server failed to restart because the cluster failover kicked in and reassigned the storage to the other node.

  8. Scott Emick says:

    I have been doing this manually for a long time, I LOVE your script.  Many thanks.

    Scott Emick

    DBA

    Cleveland, Ohio

  9. Eric Maddox says:

    Thanks. Another trick was running SSMS as administrator after doing this. It needs the elevated permissions.

  10. tim says:

    this is a great post. I think running the stored procedure as master..sp

    master..sp_addsrvrolemember is a great detail.

    The qweeblebeast says thanks.

  11. Hernan says:

    You are the man, Thank you very much!!!

  12. Perry says:

    Worked Great!