sql-serverdatabaserole

View all securables for roles in SQL Server database?


How can we show all the securable that is added in any particular role in script?


Solution

  • SELECT
        OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name
    FROM
        sys.database_permissions p
    WHERE
        p.class = 1 AND
        OBJECTPROPERTY(major_id, 'IsMSSHipped') = 0
    ORDER BY
        OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name