Sysadmins Locked Out of SQL Server – Now What?

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.

Advertisements
This entry was posted in DBA, Security and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s