How To List Object Level Permissions in SQL Server

Faruk Erdem

3 years ago

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.

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

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