See What we are Talking About!
User Based Security
By: Frank Wright, CommitDBA staff Database Administrator
November 01, 2011
Your company’s only DBA violated a company policy and is terminated effective immediately. There is no opportunity to perform any knowledge transfer. The DBA was very protective of his turf and kept a lot of information close to his vest and sometimes only existed in his memory. As an IT employee of the company, you have been temporarily tasked with performing his duties in addition to your own.
During your short tenure as interim DBA you quickly encounter your first need for sysadmin access to your main production SQL instance. You realize that you have no such access. With your limited DBA knowledge you are aware of the sa login but you were never told the password. You don’t want to bother calling him due to the nature of his termination. You’ve looked through all the documentation, searched files on his computer, and rifled through paperwork on his desk to no avail. You seemingly have no recourse, but there are several solutions to your problem.
The goal of all these solutions is to gain sysadmin access to the SQL instance for purposes of granting you sysadmin access and/or changing the SA password.
Solution #1: Logon as Administrator
One of the first things to try is logon to the server using an account that is either a local Administrator or a Domain Administrator. You can then connect to SQL via SSMS/EM using Windows Authentication. By default, administrators on the server have sysadmin access to any SQL instance running on that server. However, it’s becoming more common, recommended, and prudent to revoke sysadmin role from the BUILTIN\Administrators group inside SQL. This group is how server admins get access to SQL.
Solution #2: Logon as SQL Service Account
If you are running SQL 2000 or prior, then proceed to Solution #3. Check either Task Manager (sqlservr.exe) or Computer Management Services (SQL Server (<instance>)) for the account that SQL is running under. If the SQL instance/service in question is running under the local System account, you might not be able to use this solution. If the SQL instance/service in question is running as a domain (or local) user account, attempt to logon to the server as this account. Then connect to SQL via SSMS. When SQL 2k5 or above is installed, special groups are created on the server, one of which is SQLServer2005MSSQLUser$<servername>$<instancename>(MSSQLServer if default instance). The run as account for the SQL Server service specified at install is added to this new group by the install. This group is also granted sysadmin access inside SQL and is not standard practice to revoke this role. This applies to any login added to this group, not just the run as account. If you are running the instance as local system and another login is also added to this group, then attempt to logon to the server as that account.
Solution #3: Add Yourself to any "dba" Groups
Check the domain groups that the previous DBA’s login is a member of. If any of them appear to be labeled as a DBA account, try adding your login as a member of this group. Chances are this group was granted sysadmin access to all SQL instances as some sort of corporate standard. Your login will then have sysadmin access.
Solution #4: Logon as Former DBA
You can try this solution provided his domain account has not been deleted. If his account is merely locked/disabled, unlock his account and reset his password. Logon to the server and if his account was explicitly granted sysadmin then connect to SQL and grant yourself admin access. If the DBA relied solely on the sa login for all admin activities then this solution obviously will not work.
Solution #5: Check Registered Servers of the Former DBA
As before, you can try this solution provided his domain account has not been deleted. Logon to both his PC and the server using his logon. Launch SSMS/EM and check the list of servers he might have registered. This information is stored in the registry under each user account which is why you have to login as that user. If you are lucky and the DBA was lazy, he may have very well registered and saved the sa username/password. If so, double-click to connect to the registered server and while connected as sa, grant yourself sysadmin access and then change the sa password.
Solution #6: Use Password Cracking Utilities
I’ve seen several references to these utilities. I do not recommend using them as you can see there are several other options at your disposal. Therefore I will not discuss these any further.
Solution #7: Perform Clean Install of SQL
This is the option you will fall back on in case none of the others succeed. If you have another equivalent server available, go ahead and install SQL and make sure you patch SQL to the same build number as your current server. Restore backups of the user databases to the new server. You will need to extract definitions of all non-default logins making sure you use a script or utility that will get the hashed version of the password for any SQL authentication logins. The hashed version of the password can be used when creating a login to make sure it has the same password as previous without having to know exactly what that password was. You will also either need to restore a copy of MSDB or extract definitions of all jobs from the current server and re-create them in the new server. After that you will need to point all application connections to the new server. This option requires the most effort, downtime, and risk and that’s why it’s the option of last resort.
Solution #8: Start SQL in Single User Mode
This solution works because of a little security caveat that takes effect while in single-user mode. This mode designated by –m option in the startup is also called maintenance mode. While in maintenance mode, system Administrators are granted automatic sysadmin access to the SQL instance regardless of security privileges set for the instance in multi-user (normal) mode. To start an instance in maintenance mode you must first make some setting changes in SQL Server Configuration Manager. Under SQL Server 2005 Services, shutdown the SQL Server Agent (<instance>) service. This account will automatically grab the only connection as soon as the instance is available. Then right-click on the SQL Server (<instance>) service and choose Properties. On the advanced tab in the Startup Parameters field add ;-m to the end of the string in this field and then Click Apply then OK. Right-Click on SQL Server (<instance>) service again and select Restart. Then connect via SSMS again using Windows Authentication. Reset the sa password and/or grant yourself sysadmin access. Open up the properties of the SQL Server service again in SSCM and remove the ;-m and restart the service. SQL is back in normal operating mode and you now have sysadmin access.