Introduction
In a smaller environment or on a local computer, we use SQL server versions for learning or testing our code, etc. We do forget the system admin passwords if it’s not used for a long time. Resetting SQL Server 'sa' password is a complex issue all the time. We must have come across so many distressing times by forgetting 'sa' password of local desktop SQL or small server. We must have chosen really tough and strong passwords and we ourselves must have forgotten the password.
We are struck with so many similar incidents like all built-in administrators account from SQL Server logins has been removed, or we would have removed all the users (except SA) that were part of SYSADMIN server role (Including any Windows Accounts and/or SQL Server logins), or we use our domain user account which has DBO permissions on couple of databases but don’t have SYSADMIN privileges. These complex issues will directly affect the productivity of the organization.
Follow the quick steps in the tip in order to gain access to SQL server easily, but please note you need to have Windows administrator rights to do the below steps.
Using the Code
Open the command prompt in administrator mode.
You can start, stop, and pause SQL Server like how you would perform other services. Easily, you can do this in two ways; one is through the local system and another is through the remote system. On a local system, you can type the necessary command at a standard command prompt. On a remote system, you can connect to the system remotely and then issue the necessary command. The Command line below stops SQL Server when running as a service, where instance name is the actual name of the database server instance.
Instead of referencing MSSQLSERVER or MSSQL$instancename, you can also refer to the service by its display name. For the default instance, you use "SQL Server (MSSQLSERVER)" with net start, net stop, net pause, and net continue. For a named instance, you use net start "SQL Server (InstanceName)", where InstanceName is the name of the instance, such as. In both usages, the quotation marks are required as part of the command text.
net stop MSSQL$< InstanceName > or net stop MSSQLServer
Now, you have to start SQL Server Engine in a single-user mode. Here in the command line below, the parameter -m is used to specify 'single user mode'. For named instance, you can use MSSQL$ and then you have to connect to the server through the below SQL command line. Restart SQL Server service for the change to take effect. Once restarted, you can now connect to the SQL Server instance using SQLCMD. Once you are finished with troubleshooting, you can remove the newly added parameter and restart SQL Server service to get SQL Server back in multi-user mode.
net start MSSQL$< InstanceName > /m SQLCMD
Now connect to the server through the following SQLCMD:
sqlcmd –s LocalHostName\MyInstance
You have to open SQL Server Management Studio and connect with a Query Window using Windows Authentication without connecting to object explorer. You can do this by opening SSMS and there will be a pop up window for connecting the server. Now close this window by clicking cancel and then click new query in the side bar to open a new query window which is used to execute your SQL query.
USE [master]
GO
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = 'BUILTIN\Administrators', @rolename = 'sysadmin'
GO
Once you execute the query, all the administrators of the server will become Sysadmin. You can now add any Window account explicitly and make it sys admin and then delete the group “BUILTIN\Administrators” from SSMS security => login.
For enabling SQL authentication Sa, you have to open Object Explorer, expand Security tree and expand the Logins tree nested inside it. Now you have to right-click Sa, and then click Properties. On the General page of the properties window, you might have to create and confirm a password for the login. On the Status page, click Enabled in the Login section and then click OK.
You are done...
Now your window account must have been added to sysadmin and Sa account should have been enabled. Now the Command line below is used to stop and start the SQL server when running as a service, where instancename is the actual name of the database server instance. Once you do this, the server runs in multi user mode.
Net Stop MSSQL$< InstanceName > or Net Stop MSSQLServer
net start MSSQL$< InstanceName >
Points of Interest
Handling situations when DB are inaccessible without knowing the SA passwords is a real tough task. The above has helped me a lot when I was facing similar critical incidents of accessing databases. I'm sure this will be helpful for you too.
I would be grateful to you all if you can spare some time to give your feedback, inputs, and appreciation in the comments area. Thank you.
