Can't deny access to member of administrators group

[NullTerminator]

It seems I can't deny access to a DB for users who are directly or indirectly a member of the local administrators group. Unless I remove the user from any group that belongs to local admins, the deny has no effect.

Is this documented so IO can present to my boss, or is there a workaround?

tnx
Jeb

 

[SQLBill]

Remove the BUILTIN/ADMINISTRATORS group/role from SQL Server. That's the role tha allows local admins to have full privileges.

As for documentation: go to Microsoft's web site and do a search on BUILTIN/Administrator and SQL.


-SQLBill

 

[NullTerminator]

Just what I needed, thanks for the speedy reply.

 

[john230873]

Be careful when removing the BUILTIN/Administrator group as you services and alerts can be runing under this group and removing it can upset running of SQL Server. You should have been able to Deny the users as a Deny overrides a grant.

 

[JamesLean]

>> You should have been able to Deny the users as a Deny overrides a grant.

Yes, this is normally the case but the only time this doesn't apply is for sysadmin members. Membership of this role gives a user complete access to do anything in SQL Server and you cannot deny specific permissions.

--James

 

[SQLSister]

And make sure you either know the sa password or have set yourself up independently of the Built in Admin group as a sysadmin before you take away those rights.