Check a list of objects and permissions

--list views, procs, table

select o.* from sys.database_principals p inner join sys.objects o on p.principal_id = p.principal_id where p.name = 'GRP_App_Users' and o.type in ('U','V','P') and o.is_ms_shipped = 0


--select permissions of a group or user on a database

select p1.*,o.* from sys.database_permissions p1 inner join sys.database_principals p2 on p1.grantee_principal_id = p2.principal_id inner join sys.objects o on o.object_id = p1.major_id where p2.name = 'GRP_SQLReportingUsers'


--FIND SPS WITH EXECUTE PERMISSIONS ON A USER

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] = 'GRP_USERS_ABC') ORDER BY NAME