Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Disaster Recovery: How to regain your lost sysadmin access

By Basit Aalishan Masood-Al-Farooq,

Introduction

In this article, I will show you the steps which you require to regain your sysadmin access. Let’s pretend a situation where your company’s SQL Servers had been recently audited by external auditors.  As part of this audit it has been noted that on some of your company’s SQL Servers BUILTIN\Administrators group and logins other than DBAs are members of the sysadmin fixed server role. It has been recommended by the auditors to follow the Microsoft security best practices by removing the BUILTIN\Administrators and logins other than DBAs from the sysadmin fixed server role.

While performing this security best practice on one of your SQL Server you accidently removed your company’s DBA group from sysadmin fixed server role. To regain your sysadmin rights you decided to use SA account but you find out that SA had been disabled on this SQL Server. At this point you could rebuild the master database or reinstall SQL Server and attach all the user databases. However, this could take some time, and it doesn’t guarantee that all your logins, users, permissions, and server configurations will be recovered unless you plan to restore the master database from an old backup. You are now running out of options. What would you do?

There’s a way with which you can gain sysadmin access to your SQL Server. SQL Server 2005 and SQL Server 2008 provide a better disaster recovery option by allowing members of the Local Windows Administrators group to log in with sysadmin access whenever SQL Server is started in single-user mode, also known as "maintenance mode ". However, that would mean your Windows account will need to be a member of the Local Windows Administrators group.

Restarting SQL Server Using SQL Server Configuration Manager

You can use the SQL Server Configuration Manager tool to start SQL Server in single-user mode. This provides proper controls for the file access and other privileges. To use the Configuration Manager tool to recover your system, use the following steps:

  1. Open the Configuration Manager tool.
  2. Stop the SQL Server Instance you need to recover
  3. Navigate to the "Advanced" tab of the properties of the database engine. In Properties add ";–m" to the end of the list in the startup parameters.
  4. Click the "OK" button and restart the SQL Server Instance

NOTE: make sure there is no space between ";" and "-m".The SQL Server ERRORLOG file will include an entry that says "SQL Server started in single-user mode."

  1. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility and Windows authentication. You can use T-SQL commands such as "sp_addsrvrolemember" to add a login to the sysadmin server role.
EXEC sp_addsrvrolemember 'DomainName\DBAGroupName', 'sysadmin';
GO
  1. Once the sysadmin access has been recovered, remove the ";-m" from the startup parameters using the Configuration Manager and restart the SQL Server Instance

Restarting SQL Server using the SQL Command Line:

To use the command prompt to recover your system, use the following steps:

  1. Stop SQL Server 2005 using the command, NET STOP MSSQLSERVER
  2. Start SQL Server 2005 in Single-User mode using the command:

NET START MSSQLSERVER /m OR SQLServr.Exe –m (or SQLServr.exe –f)

Note: If the Binn folder is not in your path, you’ll need to change to the Binn folder. 

  1. Once the SQL Server service has been started in single user mode or with a minimal configuration, you can now use the SQLCMD command from a command prompt. You can connect to SQL Server with SQLCMD and perform the following operations to add yourself back as a sysadmin. 

          SQLCMD –S <Server_Name\Instance_Name>

          This will log you in to SQL Server as a sysadmin .

  1. Once you are logged into the SQL Server using SQLCMD, you can add a login to the sysadmin server role with this command. 
    EXEC sp_addsrvrolemember 'DomainName\DBAGroupName', 'sysadmin';
    GO
  1. The next step is to stop and start SQL Server services using regular startup options. (remove the –f or –m)

Conclusion:

This article shows you the process which you require to regain your sysadmin access. This process should only be used for disaster recovery when no other method to access the system with a privileged (i.e. sysadmin or equivalent) is available.

References:

Total article views: 7856 | Views in the last 30 days: 3
 
Related Articles
FORUM

Auditing "sysadmin" access to SQL Server 2005

Security Auditing Requirements for "sysadmin"

ARTICLE

Allowing BULK INSERT for non-SysAdmin Users in SQL 7.0

One of the largest security "issues" in SQL Server 7.0 is that a user must be in the SysAdmin role t...

FORUM

Trigger for sysadmin grants

Auditing server when sysadmin rights are granted

FORUM

SQL Server 2005 - Databases "not accessible" when service starts

Many databases are "not accessible" every time SQL server service is started

BLOG

Who are the sysadmins in this sql server?

Here’s a quick query that you can run to find out the users with sysadmin fixed server role. Sysadmi...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones