A system administrator can lose access to an instance of SQL Server due to following reasons:
- All logins that are members of the sysadmin fixed server role have been removed by mistake.
- All Windows Groups that are members of the sysadmin fixed server role have been removed by mistake.
- The logins that are members of the sysadmin fixed server role are for individuals who have left the company or who are not available.
- The sa account is disabled or no one knows the password.
RESOLUTION:
Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer’s local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.
Note |
When you start an instance of SQL Server in single-user mode, first stop the SQL Server Agent service. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user. |
When you use the -m option with sqlcmd or SQL Server Management Studio, you can limit the connections to a specified client application. For example, -m”sqlcmd” limits connections to a single connection and that connection must identify itself as the sqlcmd client program. Use this option when you are starting SQL Server in single-user mode and an unknown client application is taking the only available connection. To connect through the Query Editor in Management Studio, use -m”Microsoft SQL Server Management Studio – Query”.
Security Note |
Do not use this option as a security feature. The client application provides the client application name, and can provide a false name as part of the connection string.
In some configurations, SSMS will attempt to make several connections. Multiple connections will fail because SQL Server is in single-user mode. You can select one of the following actions to perform. Do one of the following.
- Connect with Object Explorer using Windows Authentication (which includes your Administrator credentials). Expand Security, expand Logins, and double-click your own login. On the Server Roles page, select sysadmin, and then click OK.
- Instead of connecting with Object Explorer, connect with a Query Window using Windows Authentication (which includes your Administrator credentials). (You can only connect this way if you did not connect with Object Explorer.) Execute code such as the following to add a new Windows Authentication login that is a member of the sysadmin fixed server role. The following example adds a domain user named CONTOSO\PatK.
CREATE LOGIN [CONTOSO\PatK] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\PatK];
- If your SQL Server is running in mixed authentication mode, connect with a Query Window using Windows Authentication (which includes your Administrator credentials). Execute code such as the following to create a new SQL Server Authentication login that is a member of the sysadmin fixed server role.
CREATE LOGIN TempLogin WITH PASSWORD = '************';
ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin;
Caution |
Replace ************ with a strong password. |
- If your SQL Server is running in mixed authentication mode and you want to reset the password of the sa account, connect with a Query Window using Windows Authentication (which includes your Administrator credentials). Change the password of the sa account with the following syntax.
ALTER LOGIN sa WITH PASSWORD = '************';
Caution |
Replace ************ with a strong password. |
|