Friday, March 30, 2012

Locked out of Enterprise Manager

Hi all

First post - please be gentle :)

I have had a search of the forum and couldn't find a solution to this one.

On a clean install of SQL Server 2000 on a W2003 server we setup our database as normal, selected appropriate priveldges for everyone and all was well.

Until an adminsitraor accidently turned builtin\administors account to DENY access. We are now locked out of EM and I can't think of a way back in.

We can't access the database via the sa account as it is set to Windows Autentication, and we can't re-register the database for the same reason.

No other user has sufficent priveldges to update the master db or change authenticaion, or even log into EM for that matter.

I think we are snookered, but any thoughts are welcomed.

Cheers

K2Interesting.
There's also one more user that has full admin rights in SQL instance, that is local admin account. i.e., machinename\administrator
Since this user also is part of admin group, try changing it's member group to POWER USERS.
Start-->Run--> control userpasswords2
Select the ADMINISTRATOR user , choose properties and under the Group Membership change it to Power Users.
Now, it won't inherit the BUILTIN/ADMINSTRATOR group , but would still be existing in sqlserver logins as admin user.
Try connecting to sql under this local admin account!

Hopefully, it would work.
AND, please don't forget to create one more ADMIN user on the machine, before changing localadmin user to POWERUSERS :)

If you couldn't connect, you might need to rebuild the master db using rebuild utility, that will leave ur instance intact but rebuild the master db and you will need to reattach ur user dbs after wards.
c:\Program Files\Microsoft SQL Server\80\Tools\Binn\rebuildm.exe|||My first thought would be to restore the master database from before the incident. There is a small chance you can reset the logon mode to mixed authentication, though. Did you ever set a password for sa?|||Interesting.
There's also one more user that has full admin rights in SQL instance, that is local admin account. i.e., machinename\administrator
Since this user also is part of admin group, try changing it's member group to POWER USERS.
Start-->Run--> control userpasswords2
Select the ADMINISTRATOR user , choose properties and under the Group Membership change it to Power Users.
Now, it won't inherit the BUILTIN/ADMINSTRATOR group , but would still be existing in sqlserver logins as admin user.

Hi

I think this would have been a better angle to take than what we did in the end! We didn't think of that one.

In the end we did a rebuild of the master database and this worked just fine.

We did try to change the authentication mode (we had set the sa password) but it wouldn't have it.

Thanks for your help guys!

K

No comments:

Post a Comment