Microsoft SQL Server: Locked out!

14/07/2021 0 Comments

Microsoft SQL Server

Sometimes you do not have the correct rights for the SQL server, but you are a domain admin, quite annoying! – this can be solved by taking the SQL offline and running in single user mode:

Step by step instructions

The following step-by-step instructions describe how to grant system administrator permissions to a SQL Server login that mistakenly no longer has access.

These instructions assume,

  • SQL Server running on Windows 8 or higher. Slight adjustments for earlier versions of SQL Server or Windows are provided where applicable.
  • SQL Server Management Studio is installed on the computer.

Perform these instructions while logged in to Windows as a member of the local administrators group.

  1. From the Windows Start menu, right-click the icon for SQL Server Configuration Manager and choose Run as administrator to pass your administrator credentials to Configuration Manager.
  2. In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the right-pane, find your instance of SQL Server. (The default instance of SQL Server includes (MSSQLSERVER) after the computer name. Named instances appear in upper case with the same name that they have in Registered Servers.) Right-click the instance of SQL Server, and then click Properties.
  3. On the Startup Parameters tab, in the Specify a startup parameter box, type -m and then click Add. (That’s a dash then lower case letter m.)

     Note

    For some earlier versions of SQL Server there is no Startup Parameters tab. In that case, on the Advanced tab, double-click Startup Parameters. The parameters open up in a very small window. Be careful not to change any of the existing parameters. At the very end, add a new parameter ;-m and then click OK. (That’s a semi-colon then a dash then lower case letter m.)

  4. Click OK, and after the message to restart, right-click your server name, and then click Restart.
  5. After SQL Server has restarted, your server will be in single-user mode. Make sure that SQL Server Agent is not running. If started, it will take your only connection.
  6. From the Windows Start menu, right-click the icon for Management Studio and select Run as administrator. This will pass your administrator credentials to SSMS.

And that’s it 🙂

 

Read more about other solutions in the article I grabbed above from:

Connect to SQL Server when system administrators are locked out – SQL Server | Microsoft Docs

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close