To get list of Server logins and database with default schema per database

Before getting entire list first get all server login list



SELECT name
        FROM SYS.SERVER_PRINCIPALS
        WHERE name NOT LIKE '#%'
        AND name NOT IN ('sa','public','sysadmin','securityadmin',
        'serveradmin','setupadmin','processadmin','diskadmin','dbcreator',
        'bulkadmin','NT AUTHORITY\SYSTEM','NT AUTHORITY\NETWORK SERVICE')
        ORDER BY name

In code below we have to add database name instead <database-name>  and union with all databases same way right now its union with 2 databases.


DECLARE @name SYSNAME

CREATE TABLE #list (databaseName NVARCHAR(100)
        ,loginname NVARCHAR(100)
        ,default_schema_name NVARCHAR(100))

DECLARE    refreshTableCursor CURSOR FOR
        SELECT name
        FROM SYS.SERVER_PRINCIPALS
        WHERE name NOT LIKE '#%'
        AND name NOT IN ('sa','public','sysadmin','securityadmin',
        'serveradmin','setupadmin','processadmin','diskadmin','dbcreator',
        'bulkadmin','NT AUTHORITY\SYSTEM','NT AUTHORITY\NETWORK SERVICE')
        ORDER BY name

    OPEN    refreshTableCursor
    FETCH NEXT FROM refreshTableCursor INTO @name
    WHILE    @@FETCH_STATUS = 0
    BEGIN
        SELECT N'<database-Name>',
                p.name,
                p.default_schema_name
        FROM sys.server_principals AS sp
        LEFT OUTER JOIN <database-name>.sys.database_principals AS p
        ON sp.sid = p.sid
        WHERE sp.name = @name
        UNION ALL
        SELECT N'<database-Name>',
                p.name,
                p.default_schema_name
        FROM sys.server_principals AS sp
        LEFT OUTER JOIN <database-name>.sys.database_principals AS p
        ON sp.sid = p.sid
        WHERE sp.name = @name
    FETCH NEXT FROM refreshTableCursor INTO @name
    END
    CLOSE        refreshTableCursor
    DEALLOCATE    refreshTableCursor   
   
SELECT    LoginName,
        DatabaseName,
        Default_Schema_Name,
        CASE WHEN is_disabled = 0 THEN 1 ELSE 0 END AS Active
FROM #list INNER JOIN sys.server_principals sp
    ON #list.LoginName = sp.name
WHERE loginname IS NOT NULL
    AND default_schema_name IS NOT NULL
ORDER BY loginname

DROP TABLE #list