How to recover the sa user password in Sql Server 2008

There are many ways to link it with our SqlServer database and one of them is to forget or not to have the user's password available to us. It may be because we never use it, because we have our own admin user and we really do not log in with that account. We may have inherited that wonderful database and we do not have any user or domain that is an administrator. Or even worse, that even from the day of installation we do not know that key and we just deleted the only login with the proper permissions ...

For any of these sad stories there may be a solution other than reinstall.

Init parameters SQL Server

From SqlServer 2005 (and up to SqlServer 2012 to my knowledge) there is, as a recovery plan for this type of disaster, the possibility of starting the SqlServer database in "single-user" mode and being able to access it with any member user Of the system administrator group.

Booting the database in single-user is intended to perform maintenance tasks, such as applying patches and performing other tasks. In our case, where we have lost the password of the user sa, will allow us, once access is limited, connect for example via sqlcmd and add a database user to the sysadmin role within SQL Server.

Here is a summary ...

Steps to follow

  1. We open the SqlServer Configuration Manager. We look for the SqlServer Service and look at the Properties, in the Advanced Options tab or Start Parameters we add an -m at the end of the line. (May vary by version). We press accept and restart the service.
     
  2. Once restarted, we open the command line and the sqlcmd client. We execute the following:
    Sqlcmd -S localhost
    1> EXEC sp_addsrvrolmember 'DOMAIN \ Dba', 'sysadmin';
     
  3. Return to the SqlServer Configuration Manager, remove the -m that we added in the first point and restart the service again. Finally we tried to login with the previous domain user and restored the sa user's password (and saved it in a safe place). Mission accomplished.

There is a variant on using the -m parameter to start the bbdd in single-user mode, it is -m "App name" (In the client application name case insensitive). This use of the parameter limits the connections to a client application with the specified name.

For example, -m "SQLCMD" limits connections to a single connection and that connection must be identified as the client program SQLCMD. You can use this option when you are starting SQL Server in single user mode and an unknown client application is using the only available connection.

To limit it to Management Studio, we use -m "Microsoft SQL Server Management Studio - Query".

 

AttachmentSize
5.SQL-Server-param_default.jpg39.57 KB