View All Permissions

The single-database audit. This query joins sys.database_permissions to sys.database_principals and returns every grant, deny or revoke in the current database - who has it, what class it applies to (database, schema, object/column or type), the resolved object name, the permission and its state.

SELECT
	USER_NAME(p.grantee_principal_id) AS PrincipalName,
	dp.type_desc AS PrincipalType,
	p.class_desc AS Class,
	CASE p.class_desc
		WHEN 'DATABASE' THEN DB_NAME(p.major_id)
		WHEN 'SCHEMA' THEN SCHEMA_NAME(p.major_id)
		WHEN 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(p.major_id)
		WHEN 'TYPE' THEN TYPE_NAME(p.major_id)
	ELSE
		''
	END AS ObjectName,
	p.permission_name AS PermissionName,
	p.state_desc AS PermissionState 
FROM
	sys.database_permissions AS p
INNER JOIN sys.database_principals AS dp
	ON p.grantee_principal_id = dp.principal_id
order by
	USER_NAME(p.grantee_principal_id)