Removing BUILTIN/Administrator Login
-
I had to leave the BUILTIN/Administrator login on my SQL Server as we had it clustered. Now, due to the vendor's product, we no longer have clustered SQL Server servers. I want to remove the BUILTIN/Administrator login, but am having a problem.
The SQL Server is on a domain (call it DOMAIN1) and I have domain sysadmin privileges. On SQL Server, I have a Windows login (call it: DOMAIN1/sqlbill). My SQL Server login has sysadmin privileges and DBO on all the databases. I tested removing the BUILTIN/Administrator login by selecting DENY ACCESS, but then I can't access SQL Server anymore. I have to use the SA account and give access back to the BUILTIN/Administrator login back in. Why? Is is because I didn't delete the login, I only gave it DENY ACCESS? If I delete BUILTIN/Administrator, will I still be able to login using my domain account?
-SQLBill
-
Bill,
no idea, but I pinged Brian Kelley. He's probably got an answer for this.
-
When you DENY access to BUILTIN/Administrator, this overrides any grants to any member of the local administrators group and therefore you cannot login.
You are not the first person with this situation. See the solution and explanation at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=166899
SQL = Scarcely Qualifies as a Language
-
You denied access to BUILTIN\Administrators, and that will affect anyone in the local Administrators group. If you have domain admin rights on the domain in question, that group is in the local administrators group by default. Therefore, you can't simply deny access to BUILTIN\Administrators.
If you've taken everything else into consideration (you're no longer clustered, so that's one thing), like granting access to NT Authority\System if you're using Full Text, you can drop Builtin\Administrators as a login. Remember, DENY is a trump. It'll block access even if the login has the ability to come in through another means (which is where you're getting stopped). However, if a person is a member of the local Administrators group has no other way in and you've dropped the BUILTIN\Administrators login, they can't get in.
You can test this by temporarily creating a local login on the SQL Server, making it a member of the local Administrators group, and logging on as that user. Before you drop BUILTIN\Administrators, the login will be able to get in. Once you drop BUILTIN\Administrators, it shouldn't be able to. This will ensure folks like you get in while others who just happen to be local administrators on the server can't.
K. Brian Kelley
@kbriankelley -
just as a general note from the first posting, you dont need to have built-in\admins on the SQL Server when clustering! As long as you put the Cluster and SQL Service Domain accounts into Local Administrators of all the nodes and also assign them as System Administrators on the SQL Server, it all works lovely! Built-in\admins should always be removed from your SQL Servers!
-
it even doesn't heed any rights but login because your clusterservices heartbeat uses it.
At our clusters the clustering service only was added as login for the server and it works fine !
Be sure you know the sa of have given explicit rights to a windowsaccount you know the password of that is sqlserver sysadmin, before you revoke builtinadmin !
Also check you sqlserver service is using a domain-account !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
This is true, though I think the MS documentation only recently has been upgraded where it says sysadmin fixed server rights aren't needed for the cluster account.
K. Brian Kelley
@kbriankelley -
Thanks for the confirmation, Brian.
We did some testing regarding the clusterserviceaccount about 3 years ago.
It works just fine with these minimal rights (login only).
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Here is the confirmation. This document was updated on 20 December 2005, so recent indeed:
How to impede Windows NT administrators from administering a clustered instance of SQL Server
The specific language from the KB article:
Before you run the Cluster Wizard, you must create the cluster service account as a login in SQL Server. This account does not need to be a member of the "Sysadmin" fixed server role. This account just needs to be able to connect and to do the "IsAlive" check.
The SQL Server and SQL Server Agent still need sysadmin fixed server role membership, but this is true of any instance of SQL Server. If Full Text is installed, NT Authority\System also needs such rights. But the language is certainly different for the cluster account than I remember.
K. Brian Kelley
@kbriankelley -
Brian and everyone else,
Thanks for the input. It looks like I shot myself in the foot by using DENY ACCESS on the account to test it and that if I had just dropped the account, I would still have been able to login (since my domain account has permissions on SQL Server).
So, I will go ahead and drop the BUILTIN\Administrator login.
-SQLBill
-
"Be sure you know the sa of have given explicit rights to a windowsaccount you know the password of that is sqlserver sysadmin, before you revoke builtinadmin !"
So, not just me who's done that one then!??
-
You're no alone ...
With this site, you'll never walk alone
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Vinny,
Thanks for the reminder, but I set the SA account and my account is a Windows account with SQL Server sysadmin privileges. My problem was my method of testing. I thought I was being smart by testing it with denying access instead of dropping it. If it didn't work, just grant access back. I forgot about the point Brian and others made, that deny is different than dropping the login.
Oh well, I dropped it and everything still works fine, except now the sysadmins who DON'T have an account on SQL Server can't get in...which was the point of this exercise.
-SQLBill
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply