View All Users Permissions

The single-database audit with inheritance. A CTE pulls every permission granted to principals - including disabled-login filtering through sys.server_principals - and then two branches union together: direct grants to users, and role-granted permissions joined back through sys.database_role_members so each row shows which role the user inherits it from. Rows that are specific to a role rather than an object get flagged with a "<Specific to role: ...>" marker.

WITH CTE as
(
	SELECT
		USER_NAME(p.grantee_principal_id) AS PrincipalName,
		dp.principal_id,
		dp.type_desc AS PrincipalType,
		p.class_desc,
		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
	LEFT OUTER JOIN sys.server_principals as sp
		ON sp.sid = dp.sid
	WHERE
		dp.type_desc = 'DATABASE_ROLE'
		OR (sp.name IS NOT NULL AND sp.is_disabled = 0))
--users
	SELECT
		p.PrincipalName,
		p.PrincipalType,
		p.class_desc AS [Class],
		p.ObjectName,
		p.PermissionName,
		p.PermissionState,
		cast('' as sysname) as InheritedFromRole
	FROM
		CTE AS p
	WHERE
		PrincipalType <> 'DATABASE_ROLE'
	UNION
	--role members
	SELECT
		rm.member_principal_name,
		rm.PrincipalType,
		IsNull(p.class_desc, IsNull('<Specific to role: ' + rm.InheritedFromRole + '>', '')),
		IsNull(p.ObjectName, IsNull('<Specific to role: ' + rm.InheritedFromRole + '>', '')),
		IsNull(p.PermissionName, IsNull('<Specific to role: ' + rm.InheritedFromRole + '>', '')) AS InheritedFromRole,
		IsNull(p.PermissionState, IsNull('<Specific to role: ' + rm.InheritedFromRole + '>', '')) AS PermissionState,
		IsNull(rm.InheritedFromRole, '')
	FROM
		CTE AS p
	RIGHT OUTER JOIN
		(
			SELECT
				role_principal_id,
				dp.type_desc as PrincipalType,
				rm.member_principal_id,
				user_name(rm.member_principal_id) as member_principal_name,
				user_name(rm.role_principal_id) as InheritedFromRole
			FROM
				sys.database_role_members AS rm
			INNER JOIN sys.database_principals AS dp
				ON rm.member_principal_id = dp.principal_id
		) AS rm
	ON rm.role_principal_id = p.principal_id
order by
	p.PrincipalName