Share via

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

  • Article

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

  • Anonymous
    December 15, 2010
    Great Post, Really useful. http://www.christowles.com

  • Anonymous
    March 07, 2012
    You saved my day man, many thanks!

  • Anonymous
    April 17, 2012
    Excellent. it worked well for me. very helpful

  • Anonymous
    October 05, 2012
    Great! Thanks

  • Anonymous
    July 30, 2013
    Hi it worked. Saved me from problems while our normal DBA was on holliday.

  • Anonymous
    November 13, 2014
    Great stuff !

  • Anonymous
    November 21, 2014
    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.

  • Anonymous
    December 14, 2014
    I have been doing this manually for a long time, I LOVE your script.  Many thanks. Scott Emick DBA Cleveland, Ohio

  • Anonymous
    April 02, 2015
    Thanks. Another trick was running SSMS as administrator after doing this. It needs the elevated permissions.

  • Anonymous
    May 20, 2015
    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.

  • Anonymous
    August 27, 2015
    You are the man, Thank you very much!!!

  • Anonymous
    October 13, 2015
    Worked Great!