Post reply

Retrieving indirectly assigned database roles for an AD user

  • May 14, 2012 at 2:42 pm

    #268866

    Hi all,

    I have the following problem:

    There are several database roles on a database. Active directory users within our domain are not directly assigned to the database roles and they also do not exist explicitly as a login on the sql server instance.

    Rather, users are assigned to the database roles via active directory groups which are assigned directly to the database roles. (I think this is the most common way to do this.)

    Now, I want to list all the implicit assigned database roles for a user logged in by integrated windows authentication.

    Due the fact that only the AD group is available in the system tables, bit not the AD user, I cannot query them directly.

    Does somebody know how to retrieve the database roles that are assigned to a AD user indirectly?

    Thx a lot!

    Bernie

  • May 14, 2012 at 2:50 pm

    #1487398

    there's an extended stored procedure to help you with that situation.

    you can use it to query an iindividual's domain login, or a group from the domain:

    try these out and see if this helps:

    -- Show all paths a user gets his auth from

    EXEC master..xp_logininfo @acctname = 'mydomain\lowell',@option = 'all'

    -- show group members

    EXEC master..xp_logininfo @acctname = 'mydomain\authenticatedusers',@option = 'members'

    --who's in the "dev " group?

    EXEC master..xp_logininfo @acctname = 'mydomain\Dev',@option = '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!

  • May 15, 2012 at 4:34 am

    #1487603

    Lowell, thank you.

    This is what helps me to get my solution ...

    Bernie

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

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