Post reply

Enumerate Windows Group Membership within SQL Server

  • August 30, 2011 at 1:40 pm

    #99415

    Hi Gurus,

    This might be a fun one.

    Environment: XP Pro (client), SQL Server 2008 R2 (database engine) on Windows Server 2008

    I'm running up against a policy, enforced by an outside entity, where we can't individually add logins to a database; users have to belong to a Windows group. So under the "Users" folder in SSMS for the database in question, you might see "Windows_Group_R", "Windows_Group_W", etc.

    My original design for determining whether a user belonged to a database role (custom in this case) was to use a view that enumerated the groups and users belonging to those groups. Example code:

    SELECT sys.database_role_members.role_principal_id, sys.database_principals.name AS role_name, sys.database_role_members.member_principal_id,

    SUBSTRING(Member.name, PATINDEX('%\%', Member.name) + 1, LEN(Member.name) - PATINDEX('%\%', Member.name) + 1) AS HID

    FROM sys.database_role_members INNER JOIN

    sys.database_principals ON sys.database_role_members.role_principal_id = sys.database_principals.principal_id INNER JOIN

    sys.database_principals AS Member ON sys.database_role_members.member_principal_id = Member.principal_id

    WHERE (sys.database_role_members.role_principal_id > 0) AND (sys.database_role_members.role_principal_id < 16384) AND

    (sys.database_principals.type = 'R')

    I can't use this technique when the "Users" are Windows groups; I can't "see" into the Windows group to look at its members. Anybody have an idea / solution? Do I have to use a Windows API call? I don't know how to do that from within a view or stored procedure.

    Any suggestions / help much appreciated.

    Steve

  • August 30, 2011 at 1:52 pm

    #1375220

    there's an extended stored proc that can help, i think

    here's an example of the syntax:

    EXEC master..xp_logininfo

    @acctname = 'mydomain\lowell',

    @option = 'all' -- Show all paths a specific user gets his auth from

    go

    EXEC master..xp_logininfo

    @acctname = 'mydomain\authenticatedusers',

    @option = 'members' -- show group members

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 30, 2011 at 1:55 pm

    #1375222

    Thank you for the reply, Lowell.

    In the production environment, that (and probably those) extended procedure call(s) are not available (permission denied).

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply