Getting locked out of a SQL Server instance can happen due to a number of situations. The most common scenario I’ve encountered is when a SQL Server is moved from one domain to another without the domain being trusted or having a local SQL admin account. I recently encountered another incident where a DBA was attempting to fail over a log-shipped instance to another instance. Part of their run book included a script to disable a set of users to block production access to the instance. The problem was, the production instance was on a cluster, unlike the development and QA systems where the script had been tested. Disabling the users in production took down the instance preventing the tail log backups from occurring. We had to get the instance back up in order to take those final backups.
What can you do if you find that you’re locked out of a SQL Server instance? The process I’ve been using for the past 13 years is to start SQL Server in single user mode, launch SQLCMD and create a new user with the system admin role. In the recent case I just mentioned, we just had to run an ALTER statement to enable the needed account. Once I have the proper account, I can restart the SQL Server Service and do what I need to do.
What are the steps to start SQL Server in single user mode and create a new sysadmin account?
I open a command prompt as an administrator with a Windows account that is a local admin. For a default instance I would then go through the following steps minus the (” “)
“net stop mssqlserver” and press enter
“net start mssqlserver /m” and press enter
“sqlcmd” and press enter
“CREATE LOGIN security WITH PASSWORD = ‘Rec0very123’ ” and press enter
“GO” and press enter
“sp_addsrvrolemember ‘security’, ‘sysadmin’ ” and press enter
“GO” and press enter
“EXIT” and press enter
“net stop mssqlserver” and press enter
“net start mssqlserver” and press enter
Now that I’ve stopped and restarted mssql in single user mode, created a new login called security and given the user security system admin rights. SQL Server Service was then stopped and started and put back in multi user mode. At this point, I can log in with the security user and complete whatever task that needs to be completed.
Named Instances are handled slightly different with having to specify the instance name.
“net stop MSSQL$SQL2017 ” and press enter
“net start MSSQL$SQL2017 /m” and press enter
“sqlcmd -Slocalhostsql2017” and press enter – localhost can also be the server name
“CREATE LOGIN security WITH PASSWORD = ‘Rec0very123’ ” and press enter
“GO” and press enter
“sp_addsrvrolemember ‘security’, ‘sysadmin’ ” and press enter
“GO” and press enter
“EXIT” and press enter
“net stop MSSQL$SQL2017 ” and press enter
“net start MSSQL$SQL2017 ” and press enter
The post How to Reset the SA Password When Locked Out appeared first on Tim Radney.