SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Builtin\Administrator
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 02/19/2008 :  10:49:32  Show Profile  Reply with Quote
I'm in the process of locking down the SQL servers here at work and I have read in several places that you should delete builtin\admin, but I have also read that you should just remove the system administrator role from this login. I'm a little confused about which I should do.

What I've done so far is:

  • added an AD group for the DBAs and given us the Sys admin role.


  • added an AD user for the SQL service


  • run sp_changedbowner to sa for each database


  • removed system administrator privileges from BUILTIN\Administrators login




When I try to remove the dbo role from builtin\admin for each database listed under database access I get the following error.

Error 15405: Cannot use the reserved user or role name 'dbo'.

I've done this on the dev server and once I'm satisfied that I've done no harm I plan to do this on all prod databases.

So, should I go ahead and delete builtin\admin? Is the dbo role listed for that login on each database an issue?

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/19/2008 :  12:19:35  Show Profile  Reply with Quote
You can delete builtin\admin as AD group for DBA already has sysadmin rights. Donot delete other groups.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 02/19/2008 :  12:29:37  Show Profile  Reply with Quote

I was pretty sure I could delete Builtin\Administrator. What I am confused about is whether I should. How do I make that determination?

What are the ramifications of leaving the login but removing the role of sysadmin and what are the ramifications of deleting the login?
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/19/2008 :  12:40:51  Show Profile  Reply with Quote
The BUILTIN\Administrators can easily be removed from SQL Server to prevent this security issue, but heed the warnings below prior to removing the group from SQL Server.

What steps should I take prior to considering removing this group?
Verify that you know the "sa" password by logging into the SQL Server with the "sa" account with either Query Analyzer or Management Studio on the SQL Server you want to modify.

Validate other Windows groups or Windows logins are assigned SQL Server System Administrator rights on this SQL Server.

Review the rights assigned to the BUILTIN\Administrators group.

Research the members of the Windows Local Administrators group.

Figure out if an additional group should be created in Active Directory and assigned rights in SQL Server or if specific logins should be assigned rights to SQL Server.

If necessary, create the additional logins and groups in Active Directory and assign rights in SQL Server to ensure a minimum of 1 login and/or the "sa" login has SQL Server System Administrator rights.

Validate that the members of the BUILTIN\Administrators group do not own any databases, objects, Jobs, etc and that none of the logins are connected to SQL Server.

If any of these steps were not completed, repeat the needed steps.

If all of these steps have been followed and you are confident that removing the BUILTIN\Administrators group will not cause any issues proceed to the next set of directions.

*** NOTE *** - Do not remove the BUILTIN\Administrators group from SQL Server if other logins or groups do not have SQL Server System Administrator rights or if you do not know the "sa" password.

With all of that being said, how do I remove the BUILTIN\Administrators group?

Method Directions
SQL Server 2000
T-SQL Commands USE MASTER
GO
exec sp_revokelogin N'BUILTIN\Administrators'
GO

OR

SQL Server 2000
Enterprise Manager Open Enterprise Manager.
Navigate to the Security folder.
Double click on the Logins icon, which will load the SQL Server logins and groups in the right pane.
Locate the BUILTIN\Administrators group.
Right click on the BUILTIN\Administrators group and select the 'Delete' option.
On the subsequent screen, read the message and if you agree press 'Yes' to remove the group.
Refresh the pane to verify the group has been dropped.


Method Directions
SQL Server 2005
T-SQL Commands DROP LOGIN [BUILTIN\Administrators]
OR

SQL Server 2005
Management Studio Open Management Studio.
Navigate to the Security folder.
Expand (+) the Logins folder, which will load the SQL Server logins and groups in the right pane.
Locate the BUILTIN\Administrators group.
Right click on the BUILTIN\Administrators group and select the 'Delete' option.
On the subsequent screen, review the screen and if you agree press 'OK' to remove the login.
Refresh the pane to verify the login has been dropped.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 02/19/2008 :  16:13:16  Show Profile  Reply with Quote
This is great. Thank you for putting it together. I have a much better understanding now.

quote:
Validate that the members of the BUILTIN\Administrators group do not own any databases, objects, Jobs, etc and that none of the logins are connected to SQL Server.


This is the one remaining problem I have. Even though I set the owners of all the databases to sa, BUILTIN\Administrators still has dbo on most of the databases. Weird.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/19/2008 :  16:23:10  Show Profile  Reply with Quote
Change the owner of DB to USer who has SYSADMIN rights.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 02/19/2008 :  16:31:58  Show Profile  Reply with Quote
quote:
Originally posted by sodeep

Change the owner of DB to USer who has SYSADMIN rights.



yeah I did that,


I ran sp_MSforeachdb 'sp_changedbowner ''sa'''

and got the following message for each one

The dependent aliases were mapped to the new database owner.
Database owner changed.



But the owner hasn't changed when I check the properties page for the individual databases.


But the good news is that builtin\administrators does not show up when I run

sp_helpsrvrolemember

Edited by - cat_jesus on 02/19/2008 16:33:46
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 02/19/2008 :  23:03:46  Show Profile  Reply with Quote
>> But the owner hasn't changed when I check the properties page for the individual databases.

Did you refresh the display?
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 02/20/2008 :  09:37:37  Show Profile  Reply with Quote
quote:
Originally posted by rmiao

>> But the owner hasn't changed when I check the properties page for the individual databases.

Did you refresh the display?



Yes, I refreshed the display and I've even restarted the SQL services and killed my client EM app.

Go to Top of Page

lesley-marshall
Starting Member

1 Posts

Posted - 02/21/2008 :  09:17:47  Show Profile  Reply with Quote
I had the exact same problem and got round it by issuing sp_revokelogin 'BUILTIN\Administrators' from Query Analyzer (instead of trying to delete using Enterprise Manager). Hope it works for you too.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.42 seconds. Powered By: Snitz Forums 2000