All Sysadmins are removed, Please help! 1

[seaport]

I was learning sql server 2005 security and making some tests on the a testing sql server. Somehow I removed sysadmin role from "BuiltIn\Administrators" login as well as that role in other logins. So basically I lost access to most function in that sql server.

I am the local administrator of that computer. I do not care if I lost data in system databases. All I want is to regain access and keep data in the user databases. How should I do that? Reinstall the SQL Server on that computer?

I am think about shutting down the SQL Server engine and delete system databases. Will SQL Server rebuild those database with a fresh start?

Thanks in advance.

Seaport

 

[JayKusch]

are you able to log into the server at all?

Thanks

J. Kusch

 

[mrdenny]

No the system will not rebuild the master database automatically if you delete it. Do you have a backup of the master database from before the rights were removed? If so you can restore the database to another server, then copy the files and overwrite the messed up mdf and ldf file for the master database.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[seaport]

I am still able to log into the SQL server with one account, which is the public role.

Denny,

I do not have any backup of the master database. I am thinking about copying a master database from another sql server, which has one sql login (with sql server authentication) as the sysadmin role. Will this work? What are possible side effects?

seaport

 

[seaport]

Here are what I did to resolve the problem.

1. On another SQL server (S2), I restored a database from a backup of its Master db and named it as TestMaster. I was afraid of messing up the S2 server but it turned out OK.
2. I detached TestMaster from S2, copied to S1 server, which I had problem with, and renamed it as master.
3. I started the sql engine of S1 and it rans OK. From the management studio I saw all user databases from S2. I deleted all of them and attached databases originally located on S1.
4. So far so good. I deleted all logins like "s2\john" and added windows logins from S1.

Here are my two questions.
1. I kept logins like "NT AUTHORITY\SYSTEM" and "BuiltIn\Administrator" because I believe they are mapped to the S1 server logins. Although my login "s1\seaport" is not in the master db in S2, I was able to log in because seaport is in the administrator of S1. Am I right?

2. All my sql logins in the restored master db did not work any more. My guess is that the those sql logins were associated with some kind of certificates on S2 machine and they certainly would not work when being used on another machine. Am I right here?

Seaport

 

[mrdenny]

1. Those logins are system logins. Someone added NT AUTHORITY\SYSTEM as it isn't added by default. The BUILTIN\Administrators is mapped to the local administrators group.

2. I'm not sure why the SQL Logins didn't work. They should have as any cert should have come through with the master database. I suppose that its possible that there was a problem with the master database certs because it was restored as a user database and that hosed something up.

Don't forget to backup your master database from now on.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[seaport]

Will do.

Thanks again, Denny.

Seaport

 

[seaport]

I ran into anohter problem with this new master database.

I could not see my database mail profiles, which are supposed to be saved in msdb. I could not create new profile either. I got the following error when I try to enable the Service Broker.

********************************
Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (BC176C5E-0932-4FB6-96CF-3BBE3C10E0B6) does not match the one in sys.databases (8636E1AC-70FB-417D-90E8-7F5A7A68AEE4).
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
*************************************

looks like I need a fresh start for the msdb also. What should I do now?

Seaport

 

[mrdenny]

What happens when you do an ALTER DATABASE msdb with NEW_BROKER?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[seaport]

Hi Denny, Thanks.

Problem solved with your tip.

alter database msdb set new_broker WITH ROLLBACK IMMEDIATE

A star for you.

Seaport

 

[mrdenny]

Excellent. Glad it got fixed.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog