By: Dan Quinones | Comments (3) | Related: > 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.
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.
Once I hit OK, 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.
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.
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".
Then bring the clustered disks and the IP address back online by right-clicking those resources and choosing "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 
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.
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 
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 
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".
All SQL Server cluster group resources should be back online.
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.
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
- Review the Microsoft articles:
- Check out other SQL Clustering tips
About the author
Dan Quinones is an Architect/Systems Engineer with over 11 years of experience specializing in Microsoft Server and Database technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
