View All Users Permissions on All Databases

The server-wide version of the permissions audit. This script walks every database with a cursor, runs a CTE-based permission query in each one via dynamic SQL, and collects the results into a single #Permissions table - including an InheritedFromRole column that traces role-granted permissions back to the individual users who hold them through role membership. Disabled logins are filtered out so you only see what is actually effective.

USE master;

DECLARE @tSQL nVarChar(Max)
DECLARE @Database nVarChar(128)

DECLARE AllDatabases CURSOR FOR
	SELECT
		name
	FROM
		sys.databases
OPEN AllDatabases

CREATE TABLE #Permissions
(
	ServerName sysname null,
	DatabaseName sysname null,
	PrincipalName sysname null,
	PrincipalType sysname null,
	[Class] sysname null,
	ObjectName sysname null,
	PermissionName sysname null,
	PermissionState sysname null,
	InheritedFromRole sysname null
)

FETCH FROM AllDatabases INTO @Database

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE

SET @tSQL =
'
USE [' + @Database + '];

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)
)

INSERT INTO #Permissions
(
	ServerName,
	DatabaseName,
	PrincipalName,
	PrincipalType,
	[Class],
	ObjectName,
	PermissionName,
	PermissionState,
	InheritedFromRole
)

--users
	SELECT
		''' + @@servername + ''',
		db_name(),
		IsNull(p.PrincipalName, ''''),
		IsNull(p.PrincipalType, ''''),
		IsNull(p.class_desc, '''') AS [Class],
		IsNull(p.ObjectName, ''''),
		IsNull(p.PermissionName, ''''),
		IsNull(p.PermissionState, ''''),
		IsNull(cast('''' as sysname), '''') as InheritedFromRole
	FROM
		CTE AS p
	WHERE
		PrincipalType <> ''DATABASE_ROLE''
	UNION
	--role members
	SELECT
		''' + @@servername + ''',
		db_name(),
		IsNull(rm.member_principal_name, ''''),
		IsNull(rm.PrincipalType, ''''),
		IsNull(p.class_desc, ''DATABASE''),
		IsNull(p.ObjectName, IsNull(''<Specific to role: '' + rm.InheritedFromRole + ''>'', '''')),
		IsNull(p.PermissionName, IsNull(''<Specific to role: '' + rm.InheritedFromRole + ''>'', '''')),
		IsNull(p.PermissionName, IsNull(''<Specific to role: '' + rm.InheritedFromRole + ''>'', '''')),
		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
	IsNull(p.PrincipalName, '''')
'

EXEC(@tSQL)

FETCH FROM AllDatabases INTO @Database
END--WHILE

CLOSE AllDatabases DEALLOCATE AllDatabases

SELECT * FROM #Permissions

DROP TABLE #Permissions