SQL Server 2008: Forgot to add an administrator account?

Note: some images appear cut off in the basic blog view. To see the full-width images, please use the link to the printer-friendly version at the end of the post. Apologies for the inconvenience.

File under: letting myself serve as an example for others.

I recently built a test box with an instance of SQL Server 2008 Developer Edition. I've done this so many times that I apparently got sloppy and stopped paying attention during setup. This time it caught up with me. On first launching Management Studio to do some post-install configuration, I realized that I'd "locked myself out" - I skipped the step to add my account to the sysadmin role on one of the instances. Setup requires that some account be added as sysadmin, but I didn't remember what I'd chosen, and there were no other accounts on the box that stood out as likely candidates.

I'd also opted to stick with the default Windows authentication mode, meaning the built-in sa account was not available.

For those who haven't made the move to SQL 2008, you'll find the following step in the Setup program. Unlike in earlier versions, local Windows admins aren't automatically granted access to SQL Server 2008. You'll want/need to designate one or more account to be added to the sysadmin server role. Skipping that step somehow got me in this mess.

I really wanted to avoid the time it would take to uninstall and reinstall the instance. Happily, I remembered another way: as a member of the local Windows Administrators group, I could start the SQL instance from a command line with the -m parameter for single user mode, connect to the service, and let myself in the "back door". ("m" for single user? Sure, that makes perfect sense. I remember this as "mono" for one at a time.)

One of the side effects of starting in single user mode is that members of the local Windows Administrator group can connect to SQL Server with its one connection. (Whether this is a good idea from a security standpoint remains open for debate, but it sure saved my bacon this time.) This would give me the ability to log in, map my Windows account into a SQL Server login, and add my new login to the sysadmin role. Here are the steps I followed.

1. Using the SQL Configuration Manager, I stopped the instance I needed to work with.
   
   
   
2. I also stopped any other SQL-related services which might try to connect to the instance, using up my one connection. This included Agent, and might also include Analysis Services or Reporting Services, depending on your installed options.
   
3. From a command prompt, I navigated to the SQL Server folder where the program executable lived.
4. I started SQL Server's executable with the -m parameter and waited for it to complete startup and recovery of any databases. (Note that the error log information echoes to the command prompt window but will simply pause when it's done startup - leave this window open to keep SQL Server running in this single-user mode. You can confirm if you successfully launched in single-user mode by looking for a line in the output that reads "SQL Server started in single-user mode. This an informational message only."
   
   
   
5. From a query tool, I connected to SQL Server using my Windows account. While I could have used a new query window in SQL Server Management Studio, I didn't want to wait for it to load, so I used SQLCMD from another command prompt session. (You can't use the Object Explorer pane in SSMS to connect when in single-user mode: clickers beware! This is one of those times where knowing the code (or having a saved script) is essential) If you're unfamiliar with SQLCMD, the -S switch specifies the instance name, the -E specifies that you're using Windows Authentication. These switches are case-sensitive.
   
6. Once connected, I created a login for my Windows account:
   
7. Then I used a system stored procedure to add my account to the sysadmin fixed server role.
   
8. Now that I've let myself back in to SQL Server, I stopped the service with the SHUTDOWN command. I could have closed the other command prompt window as well.
9. Finally, I restarted the service. Since I already had a command prompt window open, I opted for Windows' NET START command. The SQL Configuration Manager, the Windows Services Control Panel, or the Registered Servers pane in SSMS would also have worked.
   

And here's my visual confirmation (I could have simply tried to reconnect using SQLCMD, but I think we're all tired of command prompt screenshots by this point). Note that I used the system function IS_SRVROLEMEMBER to check my status. Looking at the properties of my login, or of the sysadmin role in Object Explorer would have shown this as well.

I hope this helps someone else in the same predicament!

Comments

[VDC]

a useful tip saved me having to do a reinstall!! thankyou

[Stefan]

What a relief... this method works a charm and it also taught me a lesson for future SQL 2008 installations.

THANK YOU!

[Willie]

Thanks for your help here! I couldn't get the "sqlservr -m" command to work for me. It would just bring up an Application Popup with a red X and no words. I'm sure if the server would've been able to start in single user mode that all of this would've worked wonderfully!
I'm just reinstalling rather than figure it out since it is a new install anyway.

Interesting side note: I /did/ specify an administrator user at the start. I put a domain group though (domain admins). Maybe it wants only users, not groups.

[WW]

Thank's man. Saved me plenty of time :)

[Raul Alejandro Vejar Llugany]

Thanks, very useful, although some of the screens are cropped (at least in firefox)

[Lee]

Thanks, took over machine from an old colleague and ran into this problem, helped a bunch!

[Nils Kaczenski]

Hi all,

I just found out how to use this with SQL Express. I ran into the very same "X dialog" problem. It turned out that the above procedure needs to be adjusted for SQL Express.

To launch SQL Express in single user mode append the -s parameter with the instance name, i.e.:

sqlserv.exe -m -sSQLEXPRESS

Then go ahead. Did the trick for me!

Bye, Nils

[Matt A]

Credit where credit is due. You just saved my skin... reloading SQL on these 5 servers would have been an all-nighter, so thank you a thousand times over.

[Mark Seward]

FYI ...

The "correct" way to launch the SQL Server service in -f or -m mode is to open the Services.msc Management Console snap-in. Then open the "Properties" dialog for the correct SQL instance: SQL Server or SQL Express or whichever.

Click [Stop]. Put -f or -m or -s in the "Start parameters" box. Click [Start].
The service starts & runs as always, but with your parameters in effect. Close the dialog.

When you're done with single user mode, just go back to Services.msc & click _Restart_ on your service. That will restart it without those one-time use params you supplied in the dialog.

This actually applies to any Windows service, not just SQL.

[MA Harby]

Great life saver, thanks.

Just one point, I'm running SQL Express R2, when the server was started in the command prompt, the dedicated admin port was disabled. You have to include -T7806 to the start parameters.

When you launch SQLCMD, put the -A on the command line, this tells it to use the dedicated admin port.

Mark

[Deepika]

Thanks a lot.This is really helpful.

[Matthew]

Thanks for that, it worked like a charm! I dreaded the thought of having to reinstall SQL Server.

[Chris Randall]

@Mark Seward - I'll take issue with you that the "correct" way to change the params is in the Services Control panel. Most DBAs don't use it, favoring the SQL Configuration Manager, and since I was using the command prompt to run SQLCMD and fix the problem, there's no reason to load another tool simply to pass in something I can do with a quick set of keystrokes. Clickers vs typers or something :)

@MA Harby - thanks for the additional information on the R2 trace flag!

[David T]

Thank you so much. You saved me hours of work.

One thing on step 7: I did not have to use the "@loginame" property. This worked as well:

exec addsrvrolemember 'domain\user','sysadmin'

Regardless, your instructions were flawless. Thank you.

[MarkF]

Thanks so much *again*. Saved my bacon from a reinstall too. In my case, our network changed due to a merger - then I couldn't connect anymore because my old login was obsolete and my new one was not a login - and it was windows auth only. Too bad I couldn't think far enough ahead to anticipate this.

Additional details: if you're doing this under Windows 7 as I was, a) you'll need to start the command prompts as administrator, and b) for some reason in my case I also had to provide the -d, -l and -e switches and arguments (remembering to quote the paths so that 'Program Files' doesn't mess it up). This latter may have been because I initially wasn't running the command prompt as admin - I didn't go back to test this.