locked
Explicit Login and BUILTIN\Administrators question RRS feed

  • Question

  •  

    I have a single instance of SQL Server 2005 Std SP3 running on a W2003 Server std SP2 called SRVBIZ.

     

    I have a windows domain admin account named  ROB\business.

     

    On SRVBIZ, ROB\business is a member of the Administrators group.

     

    In SQL Server, The BUILTIN\Administators group login is enabled is granted permission to connect to the database engine. So ROB\business is a member of this group.

     

    In SQL Server, ROB\business also has it's own explicit login and has dbo permissions on DB1

     

    I now deny permission to connect to the database engine for the BUILTIN\Administrators group.

     

    I log onto my PC as ROB\Business and attempt to connect to DB1 using SSMS.

     

    The login fails, despite ROB\Business having it's own explicit login. 

     

    Is this behaviour by design?  I expected that if the login could not authenticate throught the group, it would do so through it's explicit login.

     

    Thanks

    Thursday, November 11, 2010 1:58 PM

Answers

  • Permissions are cummulative and DENY takes precidence over GRANT.  Try using REVOKE instead of DENY.  This way, only granted permissions are effective.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Thursday, November 11, 2010 2:07 PM
  • Just to clarify what you did (for others who might not follow this quite right), you revoked the deny. That is, your administrators went from: GRANT -> DENY -> REVOKE (of the deny), which leaves your administrators at neutral.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, November 11, 2010 5:25 PM

All replies

  • Permissions are cummulative and DENY takes precidence over GRANT.  Try using REVOKE instead of DENY.  This way, only granted permissions are effective.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Thursday, November 11, 2010 2:07 PM
  • Thanks. Obviously you will have worked out that I haven't delved into the intricacies of the REVOKE command.

    So I dropped the explicit login and the issued this in master:-

    REVOKE CONNECT SQL FROM [BUILTIN\Administrators]
    

    Login fails.

    re-created the explicit login.

    Login suceeds.

     

     

    Thursday, November 11, 2010 3:32 PM
  • Just to clarify what you did (for others who might not follow this quite right), you revoked the deny. That is, your administrators went from: GRANT -> DENY -> REVOKE (of the deny), which leaves your administrators at neutral.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, November 11, 2010 5:25 PM
  • Not quite, as I kind of started from scratch. 

    I re-granted connect to administrators and then issued the revoke. GRANT -> REVOKE (of the grant).  

    Sorry I should have explained it better, I thought it would be obvious that I'd do it this way.

     

     

     

    Friday, November 12, 2010 9:30 AM