January 6, 2009 at 9:18 am
Hi people,
I am currently running a project in order to increase my organization's SQL Server security. The first thing that I should do is to disable the Builtin\Administrators account.
After making some research I ended up that I have either to delete the account or just remove all the roles of the account. And here comes my first question: Isn't it possible to just disable the account? When I try to do so a get an 15151 error that either the user does not exist or I don't have the rights to alter the account. I am assigned to a group with sysadmin rights by the way. So, it seems that is impossible to just disable the login. On the other hand, I can delete the user with no problems.
So if disabling the account is not possible, which is the best way to get rid of it? Deleting it or removing the rights? Which are the consequences of each choice and which is the recommended action after all?
Thanks you in advance!
January 7, 2009 at 7:04 am
In SQL 2005 you actually can disable logins.
ALTER LOGIN [BUILTIN\Administrators] DISABLE
GO
This should solve your problem.
[font="Verdana"]Markus Bohse[/font]
January 7, 2009 at 7:16 am
I tried it but it does report the error message that I mentioned:
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login 'BUILTIN\Administrators', because it does not exist or you do not have permission.
Has anybody made it?
If not which would be the best solution then? Removing or just leaving it with the "public" role? What is best?
January 7, 2009 at 7:51 am
Oops, maybe I should have tested it before posting. But if you look in BOL you will find the reason why it's not working:
You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. For example, ALTER_LOGIN [domain\group] DISABLE will return the following error message:
"Msg 15151, Level 16, State 1, Line 1
"Cannot alter the login 'Domain\Group', because it does not exist or you do not have permission."
This is by design.
So you still have to delete the Built-in\Administrators which is probably safer anyway.
[font="Verdana"]Markus Bohse[/font]
January 7, 2009 at 7:57 am
Well what does it make this safer than just removing all the roles?
What if I need to create this account in the future? It is a system account and I would like to avoid deleting. It is useful in case of a disaster recovery as far as I have read. In this case if I just remove the roles it is re-enabled. But what happens if I delete it? Can it be recreated by the system if a disaster recovery is needed?
I am quite confused and can't really take a decision..:(
January 7, 2009 at 8:50 am
T.Papachristopoulos (1/7/2009)
Well what does it make this safer than just removing all the roles?
Because public still has certain permissions and someone might add more permission to that role.
T.Papachristopoulos (1/7/2009)
What if I need to create this account in the future? It is a system account and I would like to avoid deleting. It is useful in case of a disaster recovery as far as I have read. In this case if I just remove the roles it is re-enabled. But what happens if I delete it? Can it be recreated by the system if a disaster recovery is needed?I am quite confused and can't really take a decision..:(
The only thing to remember before deleting this login is that you need to add at least one other login which has is a member of the sysadmin group.
If you need to recreate the Login you can always do that just like with any other account. Create the login and add it to the sysadmin group.
If you delete the login from SQL Server doesn't effect the permission of this group in Windows. I don't know where you read that this specific login is needed for disaster recovery, but basically any sysadmin login can do the same.
I always delete Built-in\Admins on all servers I am responsible for. It's basically part of the installation procedure.
I most companies the Domain Admins are always in the local Admin group and thus memebr of Built-in\Admins. If you would ask them to do the same the other way round (making every DBA Domain admin) they would only laugh at you. So why should they automatically have access to your possibly sensitive data ? If any one of them needs access a login can be created within seconds if necessary.
[font="Verdana"]Markus Bohse[/font]
February 11, 2009 at 7:44 am
Well, I ended up removing the sysadmin role and dyning the permission to connect to database engine for BUILTIN\Administrators group (since the login is not possible to be disabled). But again then, since that, the windows users that are not assigned to the sysadmin role started failing to connect.
I receive the following message:
Date11-02-2009 15:40:25
LogSQL Server (Current - 11-02-2009 15:40:00)
SourceLogon
Message
Error: 18456, Severity: 14, State: 11.
When I give Grant the right to connect to the database engine to BUILTIN\Administrators then these logins work correctly again. Does anybody have any idea why this happens? How can I avoid it?
February 12, 2009 at 4:59 am
The actual problem is that any domain user that is assigned to the local administrators (either directly or via a group) can not be granted the Connect to database engine right. If the connect to database right is assigned to the builtin\administrators the the user acquires it also. My sql built version is 3050. Does anybody know anything about this issue?
It could be a bug cause I cannot access the database with a local machine administrator user if the SQLServer Builtin\Administrators group is disabled, although the user does exist in the sql server logins...
:angry:
December 20, 2011 at 3:37 am
Hi all I have'nt got an answer for the above...however Im going through the same thoughfull process.
I have copied the above idea...before I delete anything I have decided to test first. I have removed Builtin\Adm..... from the sysadmin server role... and also deny permission to connect top the engine...I try this for a few days and see how it goes.
December 20, 2011 at 4:01 am
Err ok...I was just sharing my own experience on the original subject.
LOl I dont hijack...lol
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply