December 26, 2007 at 8:13 am
Can anyone help me out with a query that will document the privileges associated with a database role? Thanks,
December 26, 2007 at 8:32 am
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
.
December 26, 2007 at 8:43 am
Almost. I'm trying to create a report that documents role "xyz" as having select privileges on table_1, table_2 and view_a.
December 31, 2007 at 3:50 am
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
December 31, 2007 at 4:03 am
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]
June 28, 2010 at 4:25 am
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