SQL Server Identify User Permissions

Identifying all permissions that a user has over certain type of SQL objects can be useful for audit and assessment purposes. The script below will retrieve user permission over certain object types.


SELECT [name]
FROM sys.objects obj
INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id
WHERE obj.[type] = 'P' -- stored procedure
AND dp.permission_name = 'EXECUTE'
AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT
AND dp.grantee_principal_id = (SELECT principal_id FROM sys.database_principals WHERE [name] = 'rs')