Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Locked out of Sysdmin? Regain Sysadmin on a SQL Server 2012 Cluster


By:   |   Updated: 2013-01-10   |   Comments (3)   |   Related: More > Clustering

Problem

I recently worked on an issue with a SQL Server 2012 cluster where one of the developers informed me that while making configuration changes they removed all their user logins from the SQL sysadmin role. Users were still able to connect to SQL Server Management Studio, but they were unable to perform any administrative functions. In this tip we will look at how to restore access to the sysadmin role when you are locked out of that role.

Solution

The Microsoft article Connect to SQL Server When System Administrators Are Locked Out provides the steps to regain access when you lose access due to logins being removed by mistake or from other reasons such as administrators leaving the company or an unknown sa password. However the article does not discuss the challenges that you may face when working with a clustered SQL instance. In this tip we will discuss those challenges and present a modified approach to the Microsoft method for quickly and safely regaining administrative access to your instance.

This tip will be looking at a solution when you have no access to sysadmin and also do not have any backups of master. Although you would not have permissions to restore the master database there are other ways to regain sysadmin with the master database. Those ways include rebuilding the master database or replacing the master data and log files with those from a backup or another SQL instance. In this tip we will assume rebuilding or replacing master is not an option, you may have many user databases configured already or other constraints that make manipulating the master database a less than ideal solution.

Accidental Sysadmin Role Removal

First I'd like to point out one example of a mistake that can occur due to the behavior of the removal process. Don't remove a login and try to add one simultaneously, such as when following the SQL 2012 Best Practices Analyzer recommendations and deciding to remove unnecessary logins from the sysadmin role as seen here.

Accidental Sysadmin Role Removal

One scenario that may occur is a simultaneous add and remove by an administrator. The remove will operation work but the add will fail, leaving you locked out if you don't have an alternate login or group in the sysadmin role already. In the scenario below I am removing sqluser, I had just added sqladmin but did not commit the changes by hitting OK yet.

In the scenario below I am removing sqluser

Once I hit OK, the error appears informing me the operation failed due to insufficient permission.

the error appears informing me the operation failed due to insufficient permission

Now if we open the sysadmin role we do not have permission to see the membership and only see a limited view.

we only see a limited view

I am glad to have a domain group (domain admins) as a backup to maintain sysadmin privileges and can verify what occurred during the add/remove operation previously. As the membership proves, the remove operation succeeded and the add failed, which would have left us locked out if we didn't have a backup login or group.

a backup to maintain sysadmin privileges

Regain Sysadmin Administrative Access

To recreate the misconfiguration I removed all user logins from the sysadmin role and have no administrative access to the SQL instance. A frequently recommended fix to re-add logins to the sysadmin role is to start SQL in single user mode as described in the Microsoft article. However one of the challenges with a cluster configuration is the difficulty to connect in single user mode due to contention from cluster resources.

To begin regaining administrative access, first open the Failover Cluster Manager. Expand to the clustered SQL Server group within services and applications and take it offline with a right-click and selecting "Bring this service or application offline".

Regain Sysadmin Administrative Access

Then bring the clustered disks and the IP address back online by right-clicking those resources and choosing "Bring this resource online".

"Bring this resource online"

Open an administrative command prompt and run a net start command followed by the instance name and /m to begin the instance in single user mode.

net start mssqlserver /m

Open an administrative command prompt

Even when stopping the SQL Agent service to prevent it from using the single available connection; I found that I still could not connect and would encounter single user access errors, such as the below error, when attempting to use SSMS to connect to the instance.

 stopping the SQL Agent service

However connecting via SQLCMD works.

sqlcmd -S 192.168.1.102

Note: If you are using a named instance specify the instance name after the IP address.

You can then add a login to the sysadmin role with the sp_addsrvrolemember stored procedure.

1> sp_addsrvrolemember 'mscslab\sqladmin', 'sysadmin'
2> Go

connecting via SQLCMD works

After adding a login to sysadmin exit the sqlcmd session by typing exit.
Next run a net stop instance name to stop the instance running in single user mode.

net stop mssqlserver

adding a login to sysadmin

Restart SQL Normally

Now we are ready to start the clustered instance normally and verify that sysadmin role membership has been regained. Go back to the Failover Cluster Manager and bring the whole SQL Server group back online with a right-click and selecting "Bring this service or application online".

Restart SQL Normally

All SQL Server cluster group resources should be back online.

SQL Server cluster group

We can now log in to the instance again using SSMS and verify access to, and membership of, the sysadmin role. As you see in our scenario the sqladmin login that was originally desired now has sysadmin role membership.

log in to the instance again using SSMS

Final Thoughts

If you follow the steps in this tip you should be able to regain administrative access to your SQL Server cluster in a safe and timely manner. I hope that this tip will help other administrators and engineers save time if they find themselves stuck in a similar situation.

Next Steps


Last Updated: 2013-01-10


get scripts

next tip button



About the author
MSSQLTips author Dan Quinones Dan Quinones is an Architect/Systems Engineer with over 11 years of experience specializing in Microsoft Server and Database technologies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, January 13, 2015 - 3:15:06 PM - Ken Back To Top

This has helped rescue my SQL server.

You are a genius!

Best,

Ken


Thursday, January 24, 2013 - 8:34:02 AM - Dan Quinones Back To Top

Hi Brando,

Sure, you could certainly script certain aspects of the solution, such as the starting and stopping of the service and the stored procedure.  That is something you could run on each workstation or possibly from a central location if you have a domain or common admin account on all the workstations.

This tip was intended more for situations when a user is accidentally locked out from admin rights on a SQL cluster, but I'm glad to hear it helps to update servers running SQL Express in a different capacity as well.  

Thanks for your comment.  

Dan Quinones


Wednesday, January 23, 2013 - 11:08:14 AM - Brando Back To Top

Dan,

   Great article...is there a way to build the commands into a script? We have SQL 2008 Express (very condensed version installed that doesn't include the Management console). I was able to use you steps to add an account into the 'sysadmin' security role.

My dilema will now be how to script something that can update all our Lab workstations.

-Brando


Learn more about SQL Server tools