Locked out of SQL Server.

This is a very cool and useful tip if you find yourself locked out of the sysadmin role in sql server.

I have a SQL Server instance with the sa account disabled and all other logins removed to simulate a situation were I am locked out of SQL Server. My domain/windows account is a local administrator on my machine.

  1. Stop the SQL Server engine service using configuration manager.
  2. Open a command prompt and navigate to the binn folder of your SQL Server instance.
  3. Start SQL Server in single user mode: for a default instance:
    sqlservr -m

    For a named instance:

    sqlservr -m -s 'instance name'
  4. Open another command window and connect to the instance with sqlcmd. For a default instance:
    sqlcmd -E

    For a named instance:

    sqlcmd -E -S 'machine name\instance name'

    The -E tells sqlcmd to establish a trusted connection, and this is why we should be logged in as a local administrator at this point.

  5. Create a SQL Login:
    CREATE LOGIN rescue WITH PASSWORD = 'P@$$w0rd1';
  6. Add the login to the sysadmin group:
    EXEC sp_addsrvrolemember [rescue],[sysadmin];

  7. Go back to the first command window and stop the single user mode SQL instance by pressing <ctrl>+<break>
  8. Start the SQL Server service as normal using configuration manager, and log in with your newly created sys admin account.
  9. Now you can re add in any deleted logins, and assign sysadmin role membership as needed.
This entry was posted in Disaster Recovery, Security, SQL Server and tagged . Bookmark the permalink.

3 Responses to Locked out of SQL Server.

  1. ben says:

    life saver!

  2. Peter says:

    Great tips! Before seeing your article, I’ve unlocked the sa account using the program – SQL Server Password Changer.

  3. nag Shrenik says:

    Worked like charm ! Appreciate your help

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s