Regain sysadmin on SQL Server

Claus Munch on Jan 4, 2024 · 0 min read

Regain sysadmin on SQL Server

So, for some reason, you disabled your SA account and didn't make another accessible account sysadmin or somehow otherwise have no access to your SQL Server.

To fix this, we will need to have some downtime, but it will be minimal.

In SQL Server Configuraion Manager:
  Step 1: Stop the SQL Server Instance
  Step 2: Disable SQL Server Agent service
  Step 3: Add SQL Server startup parameter "-m", to enable Single User Mode
  Step 4: Start SQL Server Instance

Switch to a command prompt:
  Step 1: Run command "SQLCMD -Sinstance" - so in the instance of a default instance, the command would be "SQLCMD -Slocalhost", this will give us a SQL Command prompt
  Step 2: 

   CREATE LOGIN MyNewAdmin WITH PASSWORD = 'PasswordMatchingYourPolicies';
   ALTER SERVER ROLE sysadmin ADD MEMBER MyNewAdmin;
   GO

Back in SQL Server Configuration Manager:
  Step 1: Stop the SQL Server Instance
  Step 2: Enable SQL Server Agent service
  Step 3: Remove SQL Server startup parameter "-m", to disable Single User Mode
  Step 4: Start SQL Server Instance

Now, you should have regained access to your SQL Server Instance, with minimal downtime :)

About the author

Claus Munch has been working with SQL Server in all versions, since 2001. For more than 10 years, he has been running the national SQL Server usergroup. Claus is currently employed by Miracle 42, since 2020.