SQL All Users All Databases

DECLARE @Output TABLE
(database_name nvarchar(100), UserName nvarchar(100), UserType nvarchar(100), databseusername nvarchar(100), db_role nvarchar(100), permission_type nvarchar(100), permission_state nvarchar(100), object_type nvarchar(100), objectName nvarchar(100), columnname nvarchar(100))

DECLARE @DatabaseName nvarchar(100)

DECLARE @DB TABLE (db nvarchar(100))
INSERT @DB
SELECT name from sys.databases WHERE STATE_DESC = 'ONLINE'


DECLARE @Sql nvarchar(max)
SET @Sql = '
DECLARE @Output TABLE
(database_name nvarchar(100), UserName nvarchar(100), UserType nvarchar(100), databseusername nvarchar(100), db_role nvarchar(100), permission_type nvarchar(100), permission_state nvarchar(100), object_type nvarchar(100), objectName nvarchar(100), columnname nvarchar(100));




' + CHAR(13)+CHAR(10)


DECLARE dbc CURSOR FOR SELECT db FROM @DB
OPEN dbc
FETCH NEXT FROM dbc into
@DatabaseName


WHILE @@FETCH_STATUS = 0
BEGIN



print @databaseName


SET @Sql = @SQL + '


INSERT @OUTPUT
SELECT
    '''+@DatabaseName +''' AS DB,
    [UserName] = CASE princ.[type]
                    WHEN ''S'' THEN princ.[name]
                    WHEN ''U'' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN ''S'' THEN ''SQL User''
                    WHEN ''U'' THEN ''Windows User''
                 END,
    [DatabaseUserName] = princ.[name],
    [Role] = null,
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    '+ @DatabaseName + '.sys.database_principals princ
LEFT JOIN
    '+ @DatabaseName + '.sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
    '+ @DatabaseName + '.sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    '+ @DatabaseName + '.sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    '+ @DatabaseName + '.sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in (''S'',''U'')
UNION

SELECT
    '''+@DatabaseName +''' AS DB,
    [UserName] = CASE memberprinc.[type]
                    WHEN ''S'' THEN memberprinc.[name]
                    WHEN ''U'' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN ''S'' THEN ''SQL User''
                    WHEN ''U'' THEN ''Windows User''
                 END,
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    '+ @DatabaseName + '.sys.database_role_members members
JOIN
    '+ @DatabaseName + '.sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    '+ @DatabaseName + '.sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    '+ @DatabaseName + '.sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
    '+ @DatabaseName + '.sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    '+ @DatabaseName + '.sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    '+ @DatabaseName + '.sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
SELECT
    '''+@DatabaseName +''' AS DB,
    [UserName] = ''{All Users}'',
    [UserType] = ''{All Users}'',
    [DatabaseUserName] = ''{All Users}'',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    '+ @DatabaseName + '.sys.database_principals roleprinc
LEFT JOIN
    '+ @DatabaseName + '.sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    '+ @DatabaseName + '.sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
JOIN
    '+ @DatabaseName + '.sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    roleprinc.[type] = ''R'' AND
    roleprinc.[name] = ''public'' AND
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc;





' + CHAR(13)+CHAR(10)




    FETCH NEXT FROM dbc into @DatabaseName
END

CLOSE dbc
DEALLOCATE dbc

SET @SQL = @SQL + ' SELECT * FROM @Output; '

exec( @SQL )