MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter






























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

Read Comments


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.

Start SQL Server in Single User Mode

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.

Start SQL Server in Single User Mode by adding -m in Startup Parameter Tab 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.

Highly Recommended:- As a DBA your "TOP PRIORITY" should be to make sure your database backups are useful in case of any Disaster and should should know how to recover your database from a Disaster. Learn Different Types of Database Backup Options Supported in SQL Server and How to Fix Database Corruption Issues 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
Using SQLCMD Create New Login and Grant SA Privileges


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







Last Updated On: Sept 04, 2014



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: