For example, starting SQL Server with -mSQLCMD limits connections to a single connection that identifies itself as the sqlcmd client program. In order to prevent this from happening, you can use the -m option followed by an application name to limit connections to a single connection from the specified application. It's also possible for an unknown client application to take the only available connection before you're able to sign in. Otherwise, SQL Server Agent might connect first, taking the only available connection to the server and blocking you from logging in. When you start the instance in single-user mode, first stop the SQL Server Agent service. 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. You can start an instance of SQL Server in single-user mode with either the -m or -f options from the command line. Detailed steps for this solution are provided in the step-by-step-instructions section. From here, you can connect to your instance of SQL Server and add your login to the sysadmin server role. This mode prevents other connections from occurring while you try to regain access. In order to resolve your access issue, we recommend that you start the instance of SQL Server in single-user mode. The sa account is disabled or no one knows the password. The logins that are members of the sysadmin fixed server role are for individuals who have left the company or who aren't available. A system administrator can lose access to an instance of SQL Server due to one of the following reasons:Īll logins that are members of the sysadmin fixed server role have been removed by mistake.Īll Windows Groups that are members of the sysadmin fixed server role have been removed by mistake. Once the new administrator has been set up, remove the single user start-up parameter and restart the service and you should be back in business.This article describes how you can regain access to the SQL Server Database Engine as a system administrator if you've been locked out. Now we will be able to create a new administrator or add the server role to an existing login. When the database server is running in single user mode a local administrator will be able to open Management Studio and will automatically be logged in as a server administrator. This will give us access to the backdoor Microsoft has provided. The SQLCMD utility can be used as well (SQLCMD -S -m). Once the parameter has been modified, start the service from the configuration manager. To access the parameters, right click on the SQL Server service in configuration manager and select 'Properties' then click on the 'Advanced' tab. The easiest way is to modify the start-up parameters on the advanced tab from the SQL Server Configuration utility as shown below (Don't forget the semi-colon or add and spaces!). Then we need to re-start the SQL Server service in single user mode. Now you can begin by stopping the SQL Server and the SQL Server Agent services from the SQL Server Configuration Manager: If possible just use the '\administrator' account. This is very important as the procedure below will not work with a domain user that has local administrator rights on the server. Once this is done we need to log into the SQL Server as an administrator using a local account on the server. The first thing we need to do is notify our users that the systems that utilize that particular SQL Server will be down for a bit while we fix our little administration problem (Assuming that they are still up and running!). Over time accounts can be removed from Active directory that were once administrators or maybe we simply have forgotten the SA password when SQL was installed.ĭon't panic, no need to try and hack into SQL as Microsoft has given us a back door to access SQL and create ourselves a new administrator. This scenario does happen from time to time even with our best intentions to follow Microsoft best practices. If you have every found yourself locked out of SQL Server 2008 R2 it can be quite a scary experience. Modern Enterprise
0 Comments
Leave a Reply. |