• Article

Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out

This topic describes how you can regain access to the SQL Server Database Engine as a system administrator. A system administrator can lose access to an instance of SQL Server because of one of the 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.

One way in which you can regain access is to reinstall SQL Server and attach all the databases to the new instance. This solution is time-consuming; and, to recover the logins, it might require restoring the master database from a backup. If the backup of the master database is older, it might not have all the information. If the backup of the master database is more recent, it might have the same logins as the previous instance; therefore, administrators will still be locked out.

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".

Important

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.

For step-by-step instructions about how to start SQL Server in single-user mode, see How to: Configure Server Startup Options (SQL Server Configuration Manager).

Change History

Updated content

Added the description about using the -m option to limit connections to a client application.