SQL Server Permissions - Aaarrgghh!

I recently ran into a situation where I could attach to our SQL Server server but had no permission to do anything.  I couldn’t create a database, couldn’t change my permissions to allow myself access, nothing.  I couldn’t even attach to the server as sa because we were configured for Windows authentication only.  It seemed as though I was well and truly screwed.

A little (a lot actually) time spent with the programmer’s secret weapon, Google, revealed the solution.  It turns out to be a long and involved process but basically you need to enable both Windows authentication and SQL server authentication.  Set up a new sysadmin user that you can use to fix sa and then, finally, give yourself permission to do what you need to do.

Enabling SQL Server Authentication

  1. Run REGEDIT
  2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer.  This will vary depending on the version of SQL Server that you are running.
  3. Set “LoginMode” to 2.
  4. Restart SQL Server.

Setting Up a Temporary Sysadmin User

  1. Go to services.
  2. Stop SQL Server.
  3. Grab the SQL server command-line (right click the service - properties).  Mine is:
    “C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe” -sSQLEXPRESS
  4. Open an administrative command prompt.
  5. Run the command-line from step 3, but add -m -c for single-user maintenance mode command-line.
  6. Open another administrative command prompt.
  7. Run “sqlcmd -S localhost\SQLEXPRESS” from that same directory (replace with your server and instance name)
  8. Create a hero user with administrative access:
    CREATE LOGIN hero WITH PASSWORD=’123′, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF EXEC sys.sp_addsrvrolemember @loginame = ‘hero’, @rolename = ’sysadmin’
  9. QUIT and close the command-prompt
  10. Go to the SQL Server command-line window and hit ctrl+C.  It will prompt “Do you wish to shutdown SQL Server (Y/N)?” and enter Y.
  11. Close the command-prompt
  12. Restart the SQL Server service.

Altering the sa User ID

  1. Open a command propmt.
  2. Start sqlcmd using the hero user ID and password that you set up above.
    sqlcmd -U hero -P 123 -S .\SqlExpress
  3. Enable the sa user and give it a password using this command:
  4. GO
  5. Quit sqlcmd.

Giving Yourself Sysadmin Permissions

  1. Open a command prompt.
  2. Start sqlcmd using the sa user and password that you repaired above.
    sqlcmd -U sa -P newpass -S .\SqlExpress
  3. Assign yourself to the sysadmin role with this command:
    exec sp_addsrvrolemember ‘yourdomain\youruserid’,’sysadmin’
  4. Quit sqlcmd

Leave a Reply