locked
deny permissions and queries RRS feed

  • Question

  • Hi All,

    Need small help in writing 2 TSQL queries.  SQL Server version SQL 2005 ,sp3.

    Assume  i have a domain account 'domain\user'. This user can be a member of many groups which are explicitly as sqllogins.

    Query 1
    =======
    I need to know to what all sql groups this 'domain\user' account is part of.

    Query 2
    ======
    Anyone of the groups can have DENY CONNECT permissions and due to which i am not able to get connected to sql and getting below error in sql errorlog

    2012-03-07 10:04:22.19 Logon       Error: 18456, Severity: 14, State: 11.
    2012-03-07 10:04:22.19 Logon       Login failed for user 'domain\username'. [CLIENT: <local machine>]

    and state 11 is server access failure. I want to figure from where the login is getting a DENY permission.

    One more thing, I tried using xp_logininfo 'domain\username' as sql login who is a sysadmin at sql level. But i dont see any o/p. Ideally, it should show me the permission path and all.
    Strange thing i dont get any rows as output. Any reasons for that.

    But again, typing xp_loginfo for some many groups(50) is becoming difficult. That's why is there any dynamic sql queries so that i can acheive what looking for. query1 and qiery2

    Thanks in Advance. 

     

    Thursday, March 8, 2012 1:20 AM

Answers

  • As Books Online says, sp_helprotect and sp_helpuser don't understand the more granular permissions introduced in SQL Server 2005. So use sys.database_permissions and sys.database_principals instead.

    Your root problem, is that the domain knows which domain users are members of the domain groups, but SQL Server doesn't know that. That is, SQL Server waits until a user presents the domain token, and then reads it to discover the domain groups. The system must work this way, because people could be added and removed from domain groups at any time (without telling SQL Server). And people who have never connected to SQL Server might be members of domain groups who have access, and SQL Server doesn't even know they exist.

    So I think, it might be easier to use the domain tools to get lists of domain users and domain group memberships, and then check those against SQL Server Window Authentication logins. I'm aware that this might be a lot of work unless you can narrow down the area of interest. 


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Uwe RickenMVP Friday, March 9, 2012 5:30 AM
    • Marked as answer by Maggie Luo Thursday, March 15, 2012 2:26 PM
    Thursday, March 8, 2012 4:29 PM

All replies

  • Use sp_helpuser "User" to get list of all the users and the group they belong to
    Use sp_helprotect "group name" to get all the database permission of that group

    http://uk.linkedin.com/in/ramjaddu


    • Edited by RamJaddu Thursday, March 8, 2012 10:41 AM
    Thursday, March 8, 2012 10:39 AM
  • As Books Online says, sp_helprotect and sp_helpuser don't understand the more granular permissions introduced in SQL Server 2005. So use sys.database_permissions and sys.database_principals instead.

    Your root problem, is that the domain knows which domain users are members of the domain groups, but SQL Server doesn't know that. That is, SQL Server waits until a user presents the domain token, and then reads it to discover the domain groups. The system must work this way, because people could be added and removed from domain groups at any time (without telling SQL Server). And people who have never connected to SQL Server might be members of domain groups who have access, and SQL Server doesn't even know they exist.

    So I think, it might be easier to use the domain tools to get lists of domain users and domain group memberships, and then check those against SQL Server Window Authentication logins. I'm aware that this might be a lot of work unless you can narrow down the area of interest. 


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Uwe RickenMVP Friday, March 9, 2012 5:30 AM
    • Marked as answer by Maggie Luo Thursday, March 15, 2012 2:26 PM
    Thursday, March 8, 2012 4:29 PM