Connect SQL Server Configuration Manager to set startup
parameters to start SQL server in single user mode.
Click all programs and click on SQL Server, go to Configuration
Tools folder and click on SQL Server Configuration manager.
Right click on SQL Server and click properties. Click on
Startup Parameters tab. Type –m on Specify a startup parameter box and click
add.
Click ok and you will see –m added. Click add and restart
SQL services because this change won’t apply until you restart. That you will
come to know by looking at the warning.
Restart SQL services.
Now connect SQL from SSMS as administrator. Right click on
SQL Server Management Studio and select Run as Administrator option.
Connect SQL using windows authentication and navigate to sa
login. You can reset the password on this account. Note it down once you reset
it.
To test the new password, connect SQL server using sa
account. It will prompt by saying the service is in single user mode. Go to
configuration manager and remove the startup parameter we have added which is –m
and restart services. Now test the connection with sa account with new
password.
1 comment:
Actually, you can reset the 'sa' password by logging into SSMS and double-clicking on the sa login, and providing a new password in the set of text boxes that show up in the properties of the login.
Alternatively, you can update the sa password using T-SQL by opening a query window and typing
ALTER LOGIN [sa] WITH PASSWORD = 'newpasswordvalue'
This can also be done with SQLCMD or OSQL for older versions of SQL server.
The title of your article may have been more appropriate if you had named it 'How to gain access to a SQL instance where you do not know the sa password and you are not sysadmin'.
One word of caution: You said to restart SQL server services and then connect using SSMS. This will probably not work. The first connection to the database engine will be allowed, and no others. If you start the Agent, this will be the first, and you will not be able to login to work your magic. The same can be said of application services running on remote servers that connect to your instance. It is therefore more advisable to use the command prompt to do this work than the SSMS interface. You can start sql server from a command prompt by navigating to the ..\MSSQL\BINN folder and typing : sqlservr.exe -mSQLCMD
This command will start SQL in single-user mode and only allow SQLCMD connections, which will rule out agent and service connections. Leave this window open, and open another command prompt window as administrator and type SQLCMD
At this point you are in, and you can alter the 'sa' password, or better yet, create another login, like 'temp-sa', and add it to the sysadmin role. Once that is complete, close both command prompt windows and start SQL Server normally.
Logging in as temp-sa will allow you to act as sysadmin without jeopardizing any processes or connections that rely upon 'sa'.
Good luck
Post a Comment