Wednesday, February 29, 2012

I Can't Remember the Password to SQL Server User Account

I took over an instance of a Microsoft SQL Server 2008 and the login information I was given wasn't working.  Here is how to promote your domain user account to a dba sysadmin.

First you need to stop all the services for each instance.  I wrote here how to restart them.  Just stop them instead of restarting them.

Right click the service and click Properties

Click the Advanced Tab

Locate the properties box

Scroll down to Startup parameters

Move to the end of the text that is already entered.  With no spaces enter ;-m

Click Ok and start the service

This starts the service in single-user mode.

With a domain admin account, launch a command window and type sqlcmd and press enter.

You will see a >  Type the following:

EXEC sp_addsrvrolemember 'domain\user', 'sysadmin'; Hit enter.
GO Hit Enter
Type Exit, Hit Enter
NOTE:  Domain would your domain IE: and user would be jsmith.

Open SQL Server Management Studio and use Windows Authentication

At this point you should be able to now login with that user account.

Go back and stop the service, remove the ;-m and start the service again.

Source:  Link

This was successfully tested in SQL Server 2008