What is the script to get list of all objects (Tables, Views, Stored Procedures, etc...) that's within a particular user created Role?

Question

Hello, what is the script to get list of all objects (Tables, Views, Stored Procedures, etc...) that's within a particular user created Role?

Thanks in advance.

Answer 1

Hi @techresearch7777777

You can try this.

SELECT o.name AS ObjectName, o.type_desc AS ObjectType 
FROM sys.database_permissions p INNER JOIN sys.database_principals r
ON p.grantee_principal_id = r.principal_id INNER JOIN sys.objects o 
ON p.major_id = o.object_id WHERE r.name = 'RoleName'
ORDER BY o.name, o.type_desc;

Best regards,

Percy Tang