SQL SERVER: List server, database, and schema permissions for a user

ListUsersPermissions.sql

	DECLARE @user_name nvarchar(256) = null 

	SET @user_name = ISNULL(@user_name, SUSER_NAME())

	IF @user_name NOT LIKE '%\%' AND NOT EXISTS (SELECT 1 FROM master.sys.server_principals sp WHERE name = @user_name AND sp.[type_desc] = 'SQL_LOGIN') BEGIN
		SET @user_name = DEFAULT_DOMAIN() + '\' + @user_name
	END 

	IF IS_SRVROLEMEMBER('sysadmin', @user_name) = 1 BEGIN
		SELECT UPPER(@user_name) + ' IS SYSADMIN';
		RETURN;
	END

	DECLARE @schema_sql varchar(max) = ''

	DECLARE @sql nvarchar(max) = 'SET @schema_sql = ''''
		SELECT @schema_sql = @schema_sql + ''UNION SELECT ''''SCHEMA::'''' + p.entity_name, p.permission_name FROM fn_my_permissions ('''''' + s.[name] + '''''', ''''SCHEMA'''') p '' + CHAR(10) + CHAR(9)
		FROM sys.[schemas] s 
		INNER JOIN sys.all_objects ao ON [s].[schema_id] = [ao].[schema_id]
		WHERE ao.[type] = ''U'' AND s.[name] <> ''sys''
		GROUP BY s.[name]'
		
	EXEC sp_executesql @sql, N'@schema_sql varchar(max) = '''' output', @schema_sql = @schema_sql OUTPUT

	SET @sql = N'EXECUTE AS USER = ''' + @user_name + '''
	SELECT p.entity_name, p.permission_name FROM fn_my_permissions (NULL, ''DATABASE'')  p
	UNION SELECT p.entity_name, p.permission_name FROM fn_my_permissions (NULL, ''SERVER'')  p
	' + @schema_sql + '
	REVERT'
	
	--SELECT @sql
	EXEC (@SQL)