Log Shipping – NORECOVERY vs. STANDBY mode

 

I saw many people asking this question over and over again and today, I’d like to share with you a backdoor to SQL Server 2012 which would help you gain SYSADMIN access to your production SQL Servers. However, that would mean your Windows account will need to be a member of the local administrators group on Windows Servers where SQL Server 2012 Services are running.

SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.

Steps to take control of your SQL Server 2012 as an SA:

 

1. Stop the sql server services
2. Start the SQL Server 2012 instance using single user mode from command prompt by launching  the command prompt as an administrator. (Note: You can also start SQL Server 2012 using minimal configuration which will also put SQL Server in single user mode)
3. From the same command prompt ( as Administrator) type: SQLServr.Exe –m (or SQLServr.exe –f) and let the SQL Server 2012 database engine start. Make sure you do not close this command prompt window.

*You can locate SQLServr.exe in the Binn folder of your environmental path. If you don’t have SQL Server 2012 Binn folder in your environmental path, you can always navigate to the Binn folder of your SQL Server 2012 machine. Usually the Binn folder is located @ C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>

4. Once SQL Server 2012 service has been started in single user mode or with minimal configuration, you can now open up another command line window as an administrator and use SQLCMD command from command prompt to connect to SQL Server 2012 instance:

SQLCMD –S <Server_Name\Instance_Name>

                   You will now be logged in to SQL Server. Keep in mind that you are now logged in as an Admin on this server instance.

5. Create a login: 1> CREATE LOGIN 'Login_Name>' with PASSWORD='<Password>'
                                 2> GO
6. Give the user SA rights:  SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'
      

7. Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m)

8 . Log in to the SQL Server 2012 management studio or from the command prompt using “SQL_SALEEM” account and it’s respective password, you now have system admin access to your SQL Server 2012 instance. You may now reset the SA password and take control of your production SQL Server boxes.