Recover system admin permissions

[#1]

• General
• Permissions
• Solution Steps
• Detailed steps
• Single User Mode
• SQL Command Line
• Create New Account
• Assign sysadmin

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'