Sometimes we may need to list object level permissions. I will share a script that list object level permissions in SQL Server in this article. We may want to see which user has which privileges in a particular database, which user has permission in which tables, and which object (stored procedure, view, table) except for instance-based, database-based authorizations in some cases.
List Object Level Permissions in SQL Server
In such cases, you can learn by using the following t-sql statement.
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
where o.type_desc != 'SYSTEM_TABLE' and dpr.name!= 'guest' and dpr.name !='public'
order by s.name desc