Forgotten sa password

18 June,2013 by Jack Vamvas

It is frustrating to forget the sa password . Despite maintaining a password vault , it is  possible someone has changed the password or you don’t have access to the password vault. Don’t worry. If you have access to a Local administrator member than this method can help

1) Set the Single-User mode for the SQL Server Instance

go to start->run->type cmd->type services.msc->search  for SQL server (MSSQLserver) name->right click properties->

->under General tab you can see path to executable. Make a note of the sql server executable path  .

 go to start->run->type cmd->type  <sql server executable path> -m s<instance_name>

example if SQL Server Instance is called “Test1”:

 

C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn\sqlserver.exe –m sTest1

 

Note:
a) Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
b) Using the –s switch avoids the error :  Sql server installation is either corrupt or has been tampered with error getting instance id from name

2) Add a sysadmin account  through SQLCMD

 

EXEC sp_addsrvrolemember 'Org1\Johnny', 'sysadmin';
GO

 

3) Return to multiuser mode for the SQL Server Instance

 Stop the single user-mode session

Start the SQL Server Instance in multiuser mode

 

4) Change sa password

Using either SSMS or SQLCMD , reset the sa password

 

sp_password NULL,'new_password','sa'

 

Maintaining weak passwords leads to security dangers. More on the dangers of weak passwords: 10 million passwords unmasked 

 

Read More weak passwords,ALTER LOGIN and Account Locked out

Find Weak passwords in SQL Server - SQL Server DBA

sp_password and ALTER LOGIN - SQL Server DBA

Account Locked out - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Forgotten sa password


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer