cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Recover system admin permissions

Recover system admin permissions

 

General

Permissions

  1. Local administrator password for the SQL Server.
  2. Access to the server's console.

 

Solution Steps

  1. Start the SQL Server in SINGLE_USER mode (from command line or from the configuration manager).
  2. Open CMD using administrator's account.
  3. (optional) Create a new account.
  4. Assign the designated account the sysadmin server role.
  5. (optional) Reset the SA password.
  6. Return the server to MULTI_USER mode.

 

Detailed steps

Single User Mode

  1. Open the "SQL Configuration Manager Console".
  2. Navigate to "SQL Server Services"
  3. Right click on the specific instance and choose properties.
  4. Navigate to "Advanced" tab to the "Startup parameters" attribute.
  5. Add "-m;" before the existing string.
  6. Restart the SQL Server.
  • Verify that no other user is trying to connect because in SINGLE_USER only a single user can connect at a time.

 

SQL Command Line

    Using the Administrator account run from CMD

 
sqlcmd –S <host\instance>

 

 

Create New Account

 
Create LOGIN '<username>' WITH PASSWORD '<password>'

 

 

Assign sysadmin

 
SP_ADDSRVROLEMEMBER '<username>','sysadmin'
Version history
Revision #:
2 of 2
Last update:
‎May 29, 2023 07:44 PM
Updated by:
 
Contributors