Post reply

Retrive assigned server level roles for particular login using T-SQL Query

  • December 14, 2010 at 4:47 am

    #230725

    Suppose I am creating a login called 'TestLogin' and assigned Public & bulkadmin server level roles.

    I would like to retrieve the roles assigned to 'TestLogin' using query.

    How can I retrieve this data using T-SQL Query?

    Thanks

  • December 14, 2010 at 5:47 am

    #1262134

    Something like this might do the trick for you..

    SELECT p.name,p.type_desc,r.name as [server role]

    FROM

    sys.server_principals r

    INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id

    INNER JOIN sys.server_principals p ON

    p.principal_id = m.member_principal_id

    order by 1

    although server_role_members doesn't seem to hold the 'public' role, so you'll miss that one..

  • December 14, 2010 at 5:52 am

    #1262138

    jmcmullen (12/14/2010)

    Something like this might do the trick for you..

    SELECT p.name,p.type_desc,r.name as [server role]

    FROM

    sys.server_principals r

    INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id

    INNER JOIN sys.server_principals p ON

    p.principal_id = m.member_principal_id

    order by 1

    although server_role_members doesn't seem to hold the 'public' role, so you'll miss that one..

    Thanks for prompt response. It is fine.

    Thanks

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

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