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

techresearch7777777 1,801 Reputation points
2023-11-30T22:45:50.0266667+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,293 questions
0 comments No comments
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-12-01T02:06:23.2866667+00:00

    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


0 additional answers

Sort by: Most helpful