How To Reset Sa Password in Sql Server, I don't have sysadmin

Today one of application user comes to me and ask how to reset Sa password ,

  • Procedure 1: If he is not admin on sql server but have admin access on server.
  • Procedure 2: If he don't at all have access on sql server but have admin access on server.

I do remember the same scenario i have heard on DBA stack exchange a year back where some one want to change sa password and there sysadmin have left company.

Procedure 1

Step1. Go to configuration manager and add -m as startup parameter to start sql server in single user mode.

Step2. Start ssms with "Run as admininstrator"

Step3. Connect sql server with your sql account which atleast have connect access on server.

Step4. Go to logins Sa and change password.

Step5. Remove -m startup parameter and restart sql services.

Step6. Now you will be able to connect using sql account.

Procedure 2

Step1. Open Command prompt

Step2. Pass below command to connect to server

Syntax : osql -S servername\instance -E

Step3.  Execute below command to changepassword

Syntax : EXEC sp_password NULL, 'password', 'account need password change'
EXEC sp_password NULL, 'saurabh', 'sa'

Step4. Connect sql server using new password.


No comments:

Post a Comment