In this article we’ll describe the procedure of restoring access to a MS SQL Server 2012 database if the the SA password has been forgotten or there are no users with administrative privileges to manage MS SQL 2012. We’ll show how to reset the password of the SA account, and if necessary, to give another Windows account MSSQL administrator privileges.
It often happens that SA password to a MS SQL database is lost or forgotten. As a rule, SQL reinstallation or database reconnection are not considered, since in this case the configuration settings stored in the master system database will be lost. SQL Server offers a more convenient way to restore access to the database. The matter is that the members of the local administrator group in the system with SQL Server have full privileges for the database run in a single-user mode. Running SQL Server in a single-user mode, you can change the SA account password, and/or give administrative privileges to any Windows account.
You can start SQL Server 2012 in single-user mode and restore access to it as follows: (The main requirement is to have administrative privileges in the system with MSSQL installed.)
- Stop the service of the necessary SQL instance (in our case the name of this MSSQL instance is EPOSERVER) :
net stop MSSQL$EPOSERVER - Open the Registry Editor (regedit.exe) and go to the SQL start options section:
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\ MSSQL$EPOSERVER - Change ImagePath value by adding -m key as an argument of sqlservr.exe. In our example, we have got this value:
"C:\Program Files\Microsoft SQL Server\MSSQL11.EPOSERVER\MSSQL\Binn\sqlservr.exe” -m -s sEPOSERVER
- Run SQL Server:
net start MSSQL$EPOSERVER - Now SQL is run in the single-user mode and allows any member of the local administrator group to join the SQL Server instance with sysadmin rights.
- Using Management Studio, change the SA password or give the sysadmin role to any Windows account. This can be done from the sqlcmd command prompt:
EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin'; - Now you only have to stop SQL service and change ImagePart value to the original one by removing “-m” attribute (
“C:\Program Files\Microsoft SQL Server\MSSQLEPOSERVER\MSSQL\Binn\sqlservr.exe" -s sEPOSERVER) and then run SQL Server in the normal mode
Tip. When writing this article, I found out that SQL Server 2012 can also be run in the single-user mode from SQL Server Configuration Manager. To do it, you need to add “-m;” key to Startup Parameters of the necessary instance in Advanced tab.
Then follow the steps described above.
Leave a Reply