Problem
Suppose you are a DBA responsible for managing all of the SQL Server instances in your environment. For the sake of security you lock down your environment by removing the BUILTIN\Administrators group, disable sa the login, change the port number, removed all users except sa that were part of SYSADMIN server role. Now let’s assume during the locked down process, all SYSADMIN accesses has been revoked from your instance. In this case you can successfully connect to the SQL Server instance, but you cannot perform any SYSADMIN tasks. How can you grant SYSADMIN access to manage your SQL Server instance?
Solution
Below are the steps you need to perform to grant SYSADMIN access to SQL Server in case you are completely locked out.
1 – First stop the SQL Server and SQL Serve Agent services in SQL Server Management Studio.
2 – Open a cmd prompt window (Start | Run | cmd and press the ‘OK’ button) and navigate to SQL Server’s Binn directory as shown below.
3 – Once you are in SQL Server’s Binn directory run the ‘sqlservr -m’ command to start SQL Server in single user mode as shown below.
Starting SQL Server could take a few seconds to complete. Once your SQL Server instance has been started in single user mode, the following lines will be shown at the end of cmd prompt session:
4 – Now open a separate command prompt window (Start | Run | cmd and press the ‘OK’ button) and connect to your SQL Server instance by using the sqlcmd utility. If you have a named instance then use the ‘-S’ parameter with full instance name. The ‘-E’ switch is used for a trusted connection. If you are a Windows local Administrator on the machine, you will be granted SQL Server SYSADMIN rights when you connect to SQL Server in single user mode. This is the key to the access in order for this process to work.
5 – Now you are connected to your SQL Server instance with SYSADMIN rights. At this point you can add the BUILTIN\Administrators group back to the instance, create a login with SQL Server SYSADMIN rights or you can add your DBA Windows domain group as a SQL Server SYSADMIN.
6 – Assign the SQL Server sysadmin role to your newly created login:
7 – Now start the SQL Server services in SQL Server Management Studio and connect to the instance using the newly created login.
8 – Here you can validate the SQL Server sysadmin access for your new login i.e. mssqltips.
Next Steps
- Your next step would be to restore all logins or to assign/revoke appropriate accesses in SQL Server as per your company standard.
- You should also look into the root cause of the issue to determine why you have locked out completely or why all of the sysadmin access has ben revoked.
- Check out these related tips:
