SQL Server 2008: Forgot to add an administrator account?

How to recover SA password on Microsoft SQL Server 2008 R2

First start SQL service in a Single User Mode.

  • Stop the SQL services

  • Edit the properties of the SQL Service

  • Change the startup parameters of the SQL service by adding a –m; in front of the existing parameters

    • Start the SQL services. These are now running in Single User Mode.
    • Start CMD on the SQL server

 

Start the SQLCMD by using below command.

sqlcmd -S Computername\SQLEXPRESS -E

 

Now we create a new user. Enter following commands

CREATE LOGIN vijay WITH PASSWORD = ‘vijay’
go

Now we grant the user a SYSADMIN roles using the same SQLCMD window.

exec sp_addsrvrolemember ‘vijay’, ‘sysadmin’
go

  • Stop the SQL service again
  • Change the SQL service properties back to the default settings

  • Start the SQL service again and  login with newly created user(vijay in my example) with SQL Server Authentication.
  • Go to security panel and access properties of sa and change the password.

Now Change SA password.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s