October 26, 2012 at 4:47 am
Hi all,
Is it possible to list out all the tables/SP in a database and if a role has a GRANT on it what it allows...
I cant find a sys view that shows what permissions a role has been given to a table or object..
Thanks
S
October 26, 2012 at 5:33 am
n00bDBA (10/26/2012)
Hi all,Is it possible to list out all the tables/SP in a database and if a role has a GRANT on it what it allows...
I cant find a sys view that shows what permissions a role has been given to a table or object..
Thanks
S
This should get you started
SELECT CASE dbpe.[state] WHEN 'W' THEN 'GRANT'
ELSE dbpe.state_desc COLLATE Latin1_General_CI_AS
END AS [state_desc]
, dbpe.permission_name COLLATE Latin1_General_CI_AS AS perm_name
, sch.name AS sch_name
, OBJECT_NAME(dbpe.major_id) AS TheObject
, dbpr.name AS DBUsername
, CASE dbpe.[state] WHEN 'W' THEN '] WITH GRANT OPTION'
ELSE '' END AS withgrant
FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr
ON dbpr.principal_id = dbpe.grantee_principal_id
INNER JOIN sys.objects obj ON dbpe.major_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
WHERE obj.type NOT IN ('IT','S','X')
ORDER BY dbpr.name, obj.name
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 26, 2012 at 5:37 am
Thank you sir,
Id managed to get this far! yours is a lot better!!!
SELECT
p.permission_name ,
p.state_desc ,
o.name,
o.type_desc
FROM sys.database_permissions AS p
JOIN sys.objects AS o
ON p.major_id = o.object_id
ORDER BY type_desc , o.name, permission_name
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply