locked
How to find out custom role attributes? RRS feed

  • Question

  • I use SQL Server 2008. Say I create role and give it EXECUTE rights on the whole database:

    CREATE ROLE db_executor

    GRANT EXECUTE TO db_executor

    How can I find at a later time via SQL query what rights db_executor role has?
    Wednesday, October 21, 2009 6:24 PM

Answers

  • Try this

    select a.name,b.type,b.permission_name from sys.database_principals a join
    sys.database_permissions b on a.principal_id=b.grantee_principal_id
    where a.type='R' and a.name='db_executor' -- your role name here

    Vidhya Sagar. Mark as Answer if it helps!
    • Marked as answer by JoeSchmoe115 Thursday, October 22, 2009 5:23 PM
    Thursday, October 22, 2009 4:31 PM

All replies

  • Use this query and let me know were you able to acheive what you really wanted .

    select

     

    USER_NAME(role_principal_id) , user_name (member_principal_id)from sys.database_role_members


    Thanks, Leks
    Wednesday, October 21, 2009 8:15 PM
  • Use this query and let me know were you able to acheive what you really wanted .

    select

     

    USER_NAME ( role_principal_id ) , user_name ( member_principal_id ) from sys . database_role_members


    Thanks, Leks

    No, this doesn't do what I need. fn_my_permissions() function is what I was looking for
    Wednesday, October 21, 2009 9:35 PM
  • Can you Check this ?

    -- database level rights

     

    USE

     

    DBNAME;

    SELECT

     

    *

    FROM

     

    fn_my_permissions('DBNAME', 'DATABASE');

    GO

     

     

    -- object level Rights

     

    USE

     

    DBNAME;

    SELECT

     

    *

    FROM

     

    fn_my_permissions('OBJNAME', 'OBJECT')

    ORDER

     

    BY subentity_name, permission_name ;

    GO

     

     


    Thanks, Leks
    Wednesday, October 21, 2009 9:55 PM
  • Try this

    select a.name,b.type,b.permission_name from sys.database_principals a join
    sys.database_permissions b on a.principal_id=b.grantee_principal_id
    where a.type='R' and a.name='db_executor' -- your role name here

    Vidhya Sagar. Mark as Answer if it helps!
    • Marked as answer by JoeSchmoe115 Thursday, October 22, 2009 5:23 PM
    Thursday, October 22, 2009 4:31 PM