Solutions that are inaccessible to SQL Server local administrators and SAS

solutions that are inaccessible to SQL Server local administrators and SAS

Problem Description:

When installing SQL Server, you did not add the correct SQL Server local administrator account and forgot to enable mixed authentication mode. caused by SQL Server local Administrator and SA are inaccessible.

Solution:

Step 1: log in with the server's local administrator account machinename\administrator to stop the SQL Server service (no need to modify the service startup account).

Step 2: Modify the SQL Server service startup parameters, add "; M" (without double quotes) after the original startup parameters, and set SQL Server to start in single-user mode.

Step 3: start the SQL Server service and use SQLCMD to log on to the SQL Server server.

Step 4: execute the following SQL script.

Use mastergoexec sp_addsvrrolemember ' machinename\administrator ' sysadmin ' GO

Note: Please replace machinename\administrator with your local administrator account.

Step 5: Stop the SQL Server service and remove the previously added startup parameter "; M".

Step 6: start the SQL Server service.

Step 7: Authenticate with SSMs login. If the login fails, the script executed under sqlcmd fails. You need to repeat the steps above and modify steps 3 and 4 to manually add the SQL Server local administrator by using SSMS login.

Recommendation: When installing SQL Server, be careful to add the correct SQL Server local administrator and set the mixed authentication mode and SA password.

Resources:

http://blogs.msdn.com/b/raulga/archive/2007/07/12/ Disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1594603

Solutions that are inaccessible to SQL Server local administrators and SAS