question

You can use for example below query, but note that the list contains only direct rights associations. It doesn't take in mind if a principal is member of some role and the role has assigned execute permission etc (it means it doesn't show the effective permission). Also if an user is listed here and have an execute permission, it could be overridden by Deny permission of some parent group of database permission. Also it doesn't lists permissions which were granted on a schema. As if the execute permission is granted on the schema, then user have execute permissions to all the stored procedures in particular schema. The same relates to database level permissions. (you can easily alter the query to retrieve permission for database, schemas etc. For details take a look on MSDN related to the system tables mentioned in the query). So to extract the real permissions it is a more complex work. SELECT s.name AS SchemaName, o.name AS ObjectName, dp.name AS PrincipalName, dperm.type AS PermissionType, dperm.permission_name AS PermissionName, dperm.state AS PermissionState, dperm.state_desc AS PermissionStateDescription FROM sys.objects o INNER JOIN sys.schemas s on o.schema_id = s.schema_id INNER JOIN sys.database_permissions dperm ON o.object_id = dperm.major_id INNER JOIN sys.database_principals dp ON dperm.grantee_principal_id = dp.principal_id WHERE o.type IN ('P', 'PC') --P = SQL Stored Proce, PC - CLR Stored Proc AND dperm.class = 1 --object or column AND dperm.state IN ('G', 'W') AND dperm.type = 'EX'