How to reset the SA password in SQL Server
Getting locked out of a SQL Server can happen in a number of ways. You could inherit a server that was managed by someone that left the company and only that person had System Admin rights for example. A recent encounter I had was were a database server was built and provisioned in one active directory domain and then moved and joined to another non trusted domain. All the accounts provisioned within the server include those for the DBA admins were basically useless since they couldn’t be authenticated. The built in SA account is locked down per policy so no one knows the password.
What do you do if you find that you are locked out of a SQL Server instance? The way I handle it is to start SQL Server in single user mode, launch SQLCMD and create a new user. I then still using SQLCMD grant the new user the system admin role. Once I have the new user created I restart SQL Server Service and log in using the new credentials. I can then clean up the instance by granting proper access.
If this was a newly inherited server I then take the time to do a health check of the instance to make sure that everything is to our standards. (Usually this means more work)
To start SQL Server in single user mode I open a command prompt as an administrator
I type the following minus the quotes (“ “)
“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
“quit” and press enter
“net stop mssqlserver” and press enter
“net start mssqlserver” and press enter
You have now stop and restarted mssql in single user mode, created a new login called ‘security’ and given the user ‘security’ system admin rights. You then stopped and started mssql to put it back in multi user mode. I have included a screen shot of me completing the task on a development workstation so you can see the syntax and steps.
At this point you can connect to the server with SQL Server Management Studio with the new user and reset the SA password and or provision the proper people access.
9 Comments
When possible, I prefer to add a Windows group (the DBAs) over creating a SQL Server login. At some point you’re going to need to do that anyway and then you don’t have to worry about cleaning up yet another SQL Server-based login.
Brian, my preferred method is by Active Directory groups as well, however in the recent case where I had to use this process a server was moved from one domain to another that was not trusted so the provisioned accounts would not work. Once I created a new account with the SA role I was able to log back in and then provision the groups in the new domain. I wouldn’t want to imagine having to support an environment that had to grant individual rights.
Hello Tim,
Nice article. Just to add a little bit. I f you are using SQL Server 2008 R2, you can do this by impersonating the NT AUTHORITY\SYSTEM login that is created by Setup and granted sysadmin privileges by default. By this, you will not have to RESTART the sql server services. Well, this is a security loop hole which is covered in SQL Server 2012 release. I came up to this situation last week and found a great article.
Good info. Not fun when domain changes happen (hopefully not often), but good to be ready when they occur.
Thanks Tim. I know from my own experience how hard it can be to regain access to a SQL Server instance if you cannot recover the ‘sa’ password. The only step missing in this scenario is the removal from the ‘temporary’ security account. I can imagine how certain accidental DBA’s might follow your instructions and thus leave this login on a SQL Server instance with a known password and system administrator rights. It is so easy to forget this, especially in the euphoria after regaining access to important data that could not be reached by any other means.
Hi Tim
We loved your article and wanted to let you know that we included it in our Resource Roundup http://www.interworx.com/community/the-monthly-round-up-novembers-best-system-administration-hosting-security-and-enterprise-it-content/
Cheers!
Excellent post. thanks a lot for sharing. I was searching this for 2 hours and with this post it was done just in a minute
super! this trick saved my life.