As a DBA, have you ever inherited a SQL Server that you were not able to access because you were locked out of the instance as a system administrator?
- Perhaps a group within your company managed this SQL Server until the person who was responsible left the company and now it is your responsibility.
- Maybe your company just bought another company and your IT department just inherited all their hardware, but their IT staff or applications specialist did not leave any documentation.
Well, these situations are not as uncommon as you think. Over the past year, I have encountered both of these situations with multiple SQL Servers. And we shouldn’t let these situations scare us, because there is a straightforward solution that is much easier than you think. Microsoft has documented the step-by-step instructions as what to do when you are locked out as a system administrator (http://technet.microsoft.com/en-us/library/dd207004.aspx).
The basic concept is as follows:
- Configure the SQL Server service to start in single-user mode by using the database engine start-up options (http://technet.microsoft.com/en-us/library/d373298b-f6cf-458a-849d-7083ecb54ef5)
- Connect to the SQL server instance and configure the appropriate permissions (i.e. add sysadmin permissions for a domain group or account or adjust “sa” password)
- Restart the SQL Server service in multi-user mode
So, while being locked out of a newly inherited SQL Server is a problem, there is a straightforward solution to get the access you need.