SQL Server Administrator locked out of own database server

It embarrasses me to say that this happened again recently. We were building a new SQL Server 2005 server & locked it down before we had added our Administrator group & privileges to the server.

In the old days, I had assumed that I needed to reinstall SQL Server again from scratch … but I haved learned better since then! Since it has happened again, I decided it was worth blogging.

a) Start by granting a user account temporary local windows administrator privileges

b) Stop and restart the SQL Service, through the Services management console , using the -m (or -f) option. This places SQL in single user mode.

MSDN – http://msdn.microsoft.com/en-us/library/dd207004.aspx

c) Log into SQL from the command line (using the account in (a) above)

C:\>OSQL -Sservername\instance -E

d) Create a login for the Windows group

1>CREATE LOGIN [<domainName>\<win_group>] FROM WINDOWS;

2>GO

(MSDN- http://msdn.microsoft.com/en-us/library/ms189751.aspx)

e) Give SysAdmin privilege

1>EXEC master..sp_addsrvrolemember @loginame = N’domainName\win_group’, @rolename = N’sysadmin’

2>GO

http://www.sqlservercentral.com/Forums/Topic376751-146-1.aspx

and exit OSQL

f) SQL Server (& the Agent) can then be restarted without the -m to bring it back into regular service

g) TEST!!! … that the windows group can access SQL Server with system administrator privileges

h) Remove local windows privileges from the account used in (a)

In my last scenario, it was a cluster. So we had to offline the cluster SQL Service using the Cluster Admin tools. We made the -m change and started the service through the Services console. In (f) we restarted SQL using the Cluster Admin tools again.

Remember to restart any other services dependent on SQL as well.

Advertisements
Leave a comment

1 Comment

  1. Reblogged this on The Lonely DBA and commented:
    Its airways good to know how to enter in a locked database. If you have local admin access to the server this procedure might be useful. I decided to reblog this instead of writing a new post and linking to the original because it already is as simple as it gets. Cya!

    Reply

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

%d bloggers like this: