What will happen if you lost all your administrator account by mistake ? As per the best practice you have disabled the sa login. Those who had login with sysadmin rights left the company or not able to remember their password. Now you do not have any login with sysadmin rights. How do you get the administrative rights back?
I had the same kind of scenario on a server which we use very rarely. Later we realized that we lost the administrative rights on that instance and we did not had any idea to get back the sysadmin rights. The only option was destroy the instance and rebuild it.But Microsoft thought about this scenario and kept an option to recover the sysadmin rights. Let us discuss this in detail through this post.
How it works
To solve this issue, we should know two sql server start up parameter
| -f | Starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode. |
| -m | Starts an instance of SQL Server in single-user mode. When you start an instance of SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process is not started. CHECKPOINT guarantees that completed transactions are regularly written from the disk cache to the database device. Typically, this option is used if you experience problems with system databases that should be repaired. Enables the sp_configure allow updates option. By default, allow updates is disabled. Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role. |
When we start SQL server in single user mode, it is very common that application which using this server might take the connection and you will not get the connection. To avoid this situation there is an option to give the application name along with the start up parameter. For example, -mSQLCMD will restrict the connection only from an application named as SQLCMD. To connect only from SQMS, -mMicrosoft SQL Server Management Studio - Query . Note that the application name is case sensitive.Any custom build application can change its name by tweaking the connection string(How to do it?)
Now we are going to connect the instance using windows authentication method . Make sure that your windows account is part of the local administrator group of the server on which SQL server resides. Follow the below steps :
- Stop the sql server instance : This can be done by using the command prompt , SQL server configuration manager or services.msc. To stop from the command prompt, open the command prompt window with administrative rights(right click on the cmd icon and select Run as administrator) and run the command net stop mssqlserver for default instance and for named instance net stop smsqlserver$myinst replace myinst with your instance name.
- To start the SQL server in single user mode, run the following command from command prompt. net start mssqlserver /mSQLCMD for named instance,net start mssqlserver$myinst /mSQLCMD .You can do the same using the SQL server configuration manager by editing the startup parameter as given below and start the service from configuration manager.
I personally prefer to do it from the command prompt,which gives more control. Make sure that you are mentioning the correct application name with proper case. if you give application name as sqlcmd, when you try to connect it from either from commad prompt or SSMS, it will throw an error :
Login failed for user 'Mydomain\loginname'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
The error itself is misleading. When i tried , I was getting this error and took some time to find out the issue.
- Now you can connect to the sql server from the command prompt and can create a login with sysadmin rights or can assign sysadmin rights to an existing login. To connect to the SQL server from the command prompt , for default instance SQLCMD -Smyservername for named instance
SQLCMD -Smyservername\myinstancename. Then you can use the following command to create a new login and adding into sysadmin role.
USE MASTERGOCREATE login NewsysadminLogin WITH password='passwordtest123'GOsp_addsrvrolemember 'NewsysadminLogin','sysadmin'
Now the login NewsysadminLogin will have the sysadmin rights on the server . Stop the service and start it .If you have modified the startup parameter through the configuration manager , do not forget to remove the singer user switch (-m) from there.
If you liked this post, do like my page on FaceBook
www.PracticalSqlDba.com
