Why would not Deny Connect work | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Why would not Deny Connect workWe were just testing if we would be able to deny connection of Domain Administrators who are part of local administrators group on SQL Server. In SQL Server 2005 atleast the BuiltinAdministrators is principal created in SQL Server.
When we run below statement
Deny Connect SQL to [BuiltinAdministrators] it says executed successfully but they are still able to connect to SQL Server using their logins. Is it something obvious missing.
To test, create a local windows account and add that local windows account to BuiltinAdministrators group. Connect to SQL Server and deny connect to BuiltinAdministrators.
Use RunAs and open SSMS with new account it still connects.
It is because in SQL Server DENY does not – a la Windows security – apply to members of the sysadmin role and BUILTINAdministrators are members of sysadmin.
"DENY revokes a permission so that it cannot be inherited. DENY takes precedence over all permissions, except DENY does not apply to object owners or members of sysadmin. If you DENY permissions on an object to the public role it is denied to all users and roles except for object owners and sysadmin members. " (http://msdn.microsoft.com/en-us/library/bb669084.aspx).
To accomplish the same thing you can just remove the login – which isn’t a bad idea if you are not going to let them connect anyway – or disable it.