Monday, March 20, 2017

SQL Express Create System Admin Account manually

Recently I needed to manage a database that is part of a SQL Express instance.  However a coworker installed the instance, selected only Windows Authentication and his windows account was listed at the only System Admin on the instance.

How do I manage the instance without access?  Thankfully by putting the instance in Single User mode I am able to add my windows account to the instance and add it to the SysAdmin role.

** Note this will require taking the Database offline **

  1. Open the Windows Services admin tool (services.msc)
  2. Open the properties of the SQL instance in question, select the path to execute and copy the full line (i.e. "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE2008R2\MSSQL\Binn\sqlservr.exe" -sINSTANCENAME)
  3. Stop the MSSQL service.
  4. Open a Command Prompt using Run As Administrator.  (if you don't select Run As Administrator the service won't start)
  5. Paste the path copied above and add a "-m" to the end.  This will start SQL Server in single-user mode.
  6. Execute the command and you should see the SQL instance start up.  Leave this window open.
  7. Open a new command prompt using Run As Administrator.  (if you don't select Run As Administrator you will have problems connecting) 
  8. run “SQLCMD –S Server_Name\Instance_Name -E”  (the switch command are case sensitive)
  9. In this window, run the following lines, pressing Enter after each one:

    1> CREATE LOGIN [domainName\loginName] FROM WINDOWS
    2> GO
    3> SP_ADDSRVROLEMEMBER 'LOGIN_NAME','SYSADMIN'
    4> GO
  10. Use Ctrl+C in both windows to close exit the SQLCMD and Stop SQL in Single User Mode
  11. Start the SQL Instance in Services admin tool.
You should now be able to login to SQL Management Studio as SysAdmin with your credentials.