Steps to Connect to SQL Server When all System Administrators are Locked Out

Introduction

SQL Server Security is one of the key responsibilities of a Database Administrator. However, there can be scenarios when a DBA will be asked to manage SQL Server which doesn’t have any valid System Administrator or SA Accounts. This usually happens when a DBA who was managing the SQL Server Instance has left the job or SA account is disabled or when no one remembers the SA password or even when accidentally someone has removed the SA Privileges. This article explains the steps to follow to add a new login with SA privileges. The steps mentioned in this article are applicable to all editions of SQL Server 2005 and new releases.

Let us go though the steps which will help you regain SA privileges on SQL Server where all System Administrators are locked out.

Starting SQL Server in Single User Mode

1. Click Start -> Programs -> Microsoft SQL Server -> Configuration Tools -> SQL Server Configuration Manager

2. In SQL Server Configuration Manager, Stop the SQL Server Service (Database Engine).

3. Once SQL Server Service is stopped, right click the SQL Server Service on the right side panel and click on Properties.

4. In SQL Server 2008 R2 and below versions within the SQL Server Properties screen, click on Advanced Tab. Click and expand Startup Parameters and enter “;-m” as show in the snippet below. To save the configuration changes click OK.

 

5. In SQL Server 2012 and higher version Startup Parameters screen has improved and it’s now much easier to add or remove SQL Server Startup options. In the Startup Parameters tab add the “-m” parameter and click OK to save the changes. For more information on this see, Improved Startup Parameters in SQL Server 2012.

 

For more information on adding trace flags for example to identify deadlocks issues see, Identify Deadlocks in SQL Server Using Trace Flag 1222 and 1204.

6. Once you have added “-m” as a startup parameter, SQL Server Service must be restarted to start SQL Server Database Engine in Single-User Mode. For more information on different startup parameters supported in SQL Server, see Database Engine Service Startup Options.

7. Once the SQL Server Service is restarted successfully, SQL Server will start in Single User Mode. This means at this time only one user connection is allowed to the Database Engine. Hence, you must avoid starting SQL Server Agent Service or using SQL Server Object Explorer in SSMS etc.

In case a user connection already exists when SQL Server Database Engine starts in single user mode then you will see the below mentioned error message:-

Login failed for user ''. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

For more information on connecting to an SQL Server Instance when SQL Server stops responding to normal connection, see How to Use Dedicated Administrator Connection in SQL Server.

Using SQLCMD connect to SQL Server and create a new user with System Administrator Privileges

Using SQLCMD command line utility, a DBA can create a new login to the SQL Server Instance and grant System Administrative (SA) Privileges.

1. Open Command Prompt and type SQLCMD to open the utility. In SQLCMD you need to specify the below command to establish connection with SQL Server Instance which is running in single user mode.

/* If you have a default instance of SQL Server installed then use the below syntax */
sqlcmd -S <server name> -E

/* If you have a named instance of SQL Server installed then use the below syntax */
sqlcmd -S <server name\instance name> -E

/* To find help with the syntax use the below syntax*/
sqlcmd -?

2. Next step will be to create a new login and grant SA privileges. For this enter the below code and then press ENTER to create the login and grant SA privileges as shown in the snippet below. Type EXIT to come out of SQLCMD window and close the command prompt.

CREATE LOGIN DBA WITH PASSWORD ='Pa44w0rd'
GO

EXEC sp_addsrvrolemember DBA, sysadmin
GO

 

3. Final step will be to remove “-m” startup parameter and restart the SQL Server Service. Now open SQL Server Management Studio and connect to SQL Server Instance using the newly created login which has SA privileges.

Next Steps

• Once you have connected to SQL Server with System Administrator (SA) Privileges using the new login you must change the password of SA login. For more information, see How to Change SA Password in SQL Server.
• If you haven't enabled DAC in SQL Server then see, Different Ways to Enable Dedicated Administrator Connection in SQL Server.
• For more information on Best Practices for DBAs and Developers read “SQL Server Best Practices”

Read more: http://www.mytechmantra.com/LearnSQLServer/Steps-to-Connect-to-SQL-Server-When-all-System-Administrators-are-Locked-Out/#ixzz3h4bTW5gY
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook