Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 | select permission_name,s.name,o.name,dpr.name,o.type_desc 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 |
