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