Role Permissions

  • Can anyone help me out with a query that will document the privileges associated with a database role? Thanks,

  • something like this?

    select pri.name, per.permission_name from sys.database_permissions per

    INNER JOIN sys.database_principals pri

    ON per.grantee_principal_id = pri.principal_id

    .

  • Almost. I'm trying to create a report that documents role "xyz" as having select privileges on table_1, table_2 and view_a.

  • The attached SP should do the trick...

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Sorry I can't open Ed's procedure at the moment but this is a script I use:

    SELECT Rolename,

    [Objectname],

    [Objecttype],

    [Execute],

    [Select],

    [Insert],

    [Update],

    [Delete],

    [References]

    FROM (SELECT P.Name AS Rolename,

    (S.Name + '.' + O.Name) AS [objectname],

    O.TYPE AS [objecttype],

    Dp.Permission_name,

    Dp.State_desc

    FROM Sys.Database_permissions Dp

    JOIN Sys.Database_principals P

    ON P.Principal_id = Dp.Grantee_principal_id

    JOIN Sys.Objects O

    ON Dp.Major_id = O.[object_id]

    JOIN Sys.Schemas S

    ON O.[schema_id] = S.[schema_id]

    WHERE P.TYPE = 'R'

    AND P.Principal_id < 16000

    AND Dp.Class = 1

    UNION

    SELECT P.Name AS Rolename,

    (S.Name) AS [objectname],

    'Sch',

    Dp.Permission_name,

    Dp.State_desc

    FROM Sys.Database_permissions Dp

    JOIN Sys.Database_principals P

    ON P.Principal_id = Dp.Grantee_principal_id

    JOIN Sys.Schemas S

    ON Dp.Major_id = S.[schema_id]

    WHERE P.TYPE = 'R'

    AND P.Principal_id < 16000

    AND Dp.Class = 3) P

    PIVOT

    (MAX(State_desc)

    FOR Permission_name IN ( [EXECUTE],[SELECT],[INSERT],[UPDATE],[DELETE],[REFERENCES] ) ) AS Pvt

    ORDER BY Rolename,

    [Objectname]

    [font="Verdana"]Markus Bohse[/font]

  • I need something that is sort of the opposite of this script. This script shows me all of the objects that the database role can access. I need to know all of the objects that I forgot to grant access to. Sometimes when I create new tables or views I forget the corresponding "grant" statements. I do my development on my local computer as administrator. Then when I go into production I discover that I missed something and the application won't work for non-administrative users.

    Even a list of all objects with the names of the roles that have access (select, delete, etc.). Then I could quickly scan down the list to find objects that do not for example have any role with select access.

Viewing 6 posts - 1 through 5 (of 5 total)

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