SQL Server Performance

Why would not Deny Connect work

Discussion in 'SQL Server 2005 General DBA Questions' started by gurucb, Jan 8, 2009.

  1. gurucb New Member

    We 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.
  2. Elisabeth Redei New Member

    Hi,
    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.
    HTH
    /Elisabeth

Share This Page