disabling BUILTIN\Administrators
-
Hi all,
We recently had a change in policy that requires the BUILTIN\Administrators group on all SQL Server instances (SQL Server 2k, 05, Biztalk 04, and Biztalk 06 environments) be disabled. Since these databases have been in operation long before I got here, I'm concerned with the implications of doing this. Other than owned jobs, databases, etc, does anyone have any good advice on what to check before doing this? I logged in as SA to a DB where the BUILTIN\Admin group owned nothing and tried to disable it, but I received the "cannot alter login 'BUILTIN\Administrators', because it does not exist or you do not have permission" message. Any advice/thoughts/instructions on how to do this as cleanly as possible would be greatly appreciated.
Thanks.
-
Y, when the time comes you'll have to actually delete the group.
Make sure that your service accts are explicitely given sa or you'll be in trouble.
Otherwise, if nothing's owned, then you should be ok.
I would take this time also to look in that group on the windows side and see who's in it.
You'll need to give anyone rights who is currently getting into SQL via membership in that group.
So just check the local administrators group for a quick head count.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Actually, that's what caused this problem. Network personnel are not to have any access to SQL, and the DBAs are not to have any Windows access at all. So it sounds like disabling the group won't work? It has to be deleted? I will scour the set up as best I can then. Especially with Biztalk since I'm not too sure if some set up accounts for it require that SQL group.
-
That logic is flawed on its face.
If you're using Windows auth, the DBAs have to have access to windows. SQL checks windows auth before it checks SQL rights. And even if you're going with sql auth, someone has to keep the password to the service accts. Since the service accts have obvious rights on the OS, then DBAs who have that password automatically have rights on the OS. If the network admins are in charge of the password, then they have sa in SQL automatically.
The DBAs should be in charge of the service acct passwords since it's more likely that there's something in the DB that the network admins shouldn't see than it is that there's something in the OS that the DBAs can't see. It just doesn't make sense to lock the DBAs out of the OS. We quite often have to check local disks for one thing or another, write files locally for backups, and pull perfmon counters for baselines and/or troubleshooting. Network admins never have any reason to be in the DB.
So they're asking for trouble locking the DBAs out of the OS. And like I said, someone's gotta have the keys to both castles, and it makes more sense for it to be the DBAs because of their crossover into the OS.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Whoa. There's a lot of stuff being thrown out, let's work through it.
BUILTIN\Administrators corresponds to the local Administrators security group for the OS. Before you go about looking to revoke that login, things to take care of:
If the SQL Server DBAs are coming in to SQL Server via this security group, you'll need to ensure that their appropriate security group is granted access and given the sysadmin fixed server role rights.
Verify the SQL Server and SQL Server Agent service accounts are added explicitly and given sysadmin fixed server role rights.
If you have a cluster, make sure the cluster service account is granted the ability to log in. It doesn't need anything more. It just does a SELECT @@VERSION or SELECT @@SERVERNAME (I forget which), which any user can run.
If you have Full Text installed and it's SQL Server 7 or 2000, make sure the NT AUTHORITY\SYSTEM is granted the ability to log in to SQL Server and make it a member of the sysadmin fixed server role.
You'll then need to check any other accounts that may be coming in via BUILTIN\Administrators, such as service accounts for applications. Technically, they shouldn't need sysadmin rights, but you need to verify if there are any and then recreate the logins and set the permissions needed.
Any member that is a member of the sysadmin fixed server role (this would include BUILTIN\Administrators by default) maps into the database as dbo. That means you should not have any database objects (tables, views, stored procedures, etc.) that map to one of these logins that will be removed, but you still want to check. Databases can be owned by a particular account, and by default the account that created the database is the owner. This is one area you DO want to check. You've already indicated jobs. Those, too.
Then take it in steps. First, make sure you know the sa password, or have an account that can get in if the BUILTIN\Administrators doesn't have the full rights. If you're DBA security group was added and has appropriate rights, you're set. The SQL Server service account is another way in.
Take a backup of the master database and verify its integrity. If you have a test system that's off-line from the network, test the restore of that backup (USB key or something similar to transfer the backup file). Make sure it's good.
You can always recover SQL Server 7 or 2000 using rebuildm.exe and restoring the master database. If it's SQL Server 2005, sqlservr.exe -m -c (and an additional -s **instance name** if it's a named instance) at the command line starts it up in single user mode where you can connect locally and in an emergency add BUILTIN\Administrators back in or make them a member of the sysadmin fixed server role.
Next, remove the membership from the sysadmin fixed server role. This still leaves BUILTIN\Administrators as being able to log in to the server, but it doesn't give them the full rights. If you have errors after doing so, troubleshoot and resolve.
Finally, move to removing BUILTIN\Administrators. Again, look for issues.
If no more issues, you're done!
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply