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