SCRIPT: Compare users on two databases

 /*
=============================================================================
T-SQL Script: Compare Database Users Between Two Databases
Purpose: Identify users that exist in one database but not the other
=============================================================================
*/
 
-- Set the database names to compare
DECLARE @Database1 NVARCHAR(128) = 'DB1';
DECLARE @Database2 NVARCHAR(128) = 'DB2';
 
-- Display header
PRINT '========================================';
PRINT 'Database User Comparison Report';
PRINT '========================================';
PRINT 'Database 1: ' + @Database1;
PRINT 'Database 2: ' + @Database2;
PRINT '========================================';
PRINT '';
 
-- Create temp tables to store user information
IF OBJECT_ID('tempdb..#DB1Users') IS NOT NULL DROP TABLE #DB1Users;
IF OBJECT_ID('tempdb..#DB2Users') IS NOT NULL DROP TABLE #DB2Users;
 
CREATE TABLE #DB1Users (
    UserName NVARCHAR(128),
    UserType NVARCHAR(60),
    DefaultSchema NVARCHAR(128),
    CreateDate DATETIME,
    ModifyDate DATETIME
);
 
CREATE TABLE #DB2Users (
    UserName NVARCHAR(128),
    UserType NVARCHAR(60),
    DefaultSchema NVARCHAR(128),
    CreateDate DATETIME,
    ModifyDate DATETIME
);
 
-- Get users from Database 1
DECLARE @SQL1 NVARCHAR(MAX) = N'
USE [' + @Database1 + N'];
SELECT
    name AS UserName,
    type_desc AS UserType,
    default_schema_name AS DefaultSchema,
    create_date AS CreateDate,
    modify_date AS ModifyDate
FROM sys.database_principals
WHERE type IN (''S'', ''U'', ''G'', ''C'', ''K'', ''E'', ''X'')
    AND name NOT IN (''guest'', ''INFORMATION_SCHEMA'', ''sys'')
    AND name NOT LIKE ''##%'';
';
 
INSERT INTO #DB1Users
EXEC sp_executesql @SQL1;
 
-- Get users from Database 2
DECLARE @SQL2 NVARCHAR(MAX) = N'
USE [' + @Database2 + N'];
SELECT
    name AS UserName,
    type_desc AS UserType,
    default_schema_name AS DefaultSchema,
    create_date AS CreateDate,
    modify_date AS ModifyDate
FROM sys.database_principals
WHERE type IN (''S'', ''U'', ''G'', ''C'', ''K'', ''E'', ''X'')
    AND name NOT IN (''guest'', ''INFORMATION_SCHEMA'', ''sys'')
    AND name NOT LIKE ''##%'';
';
 
INSERT INTO #DB2Users
EXEC sp_executesql @SQL2;
 
PRINT '';
PRINT '========================================';
PRINT 'Reference: All Users in ' + @Database1;
PRINT '========================================';
 
SELECT UserName, UserType, DefaultSchema
FROM #DB1Users
ORDER BY UserName;
 
PRINT '';
PRINT '========================================';
PRINT 'Reference: All Users in ' + @Database2;
PRINT '========================================';
 
SELECT UserName, UserType, DefaultSchema
FROM #DB2Users
ORDER BY UserName;
 
PRINT '';
-- Find users in Database 1 but NOT in Database 2
PRINT '----------------------------------------';
PRINT 'Users in ' + @Database1 + ' but MISSING from ' + @Database2;
PRINT '----------------------------------------';
 
SELECT
    db1.UserName,
    db1.UserType,
    db1.DefaultSchema,
    db1.CreateDate,
    db1.ModifyDate,
    'Missing from ' + @Database2 AS Status
FROM #DB1Users db1
WHERE NOT EXISTS (
    SELECT 1
    FROM #DB2Users db2
    WHERE db2.UserName = db1.UserName COLLATE DATABASE_DEFAULT
)
ORDER BY db1.UserName;
 
DECLARE @MissingFromDB2 INT = @@ROWCOUNT;
 
PRINT '';
PRINT '----------------------------------------';
PRINT 'Users in ' + @Database2 + ' but MISSING from ' + @Database1;
PRINT '----------------------------------------';
 
-- Find users in Database 2 but NOT in Database 1
SELECT
    db2.UserName,
    db2.UserType,
    db2.DefaultSchema,
    db2.CreateDate,
    db2.ModifyDate,
    'Missing from ' + @Database1 AS Status
FROM #DB2Users db2
WHERE NOT EXISTS (
    SELECT 1
    FROM #DB1Users db1
    WHERE db1.UserName = db2.UserName COLLATE DATABASE_DEFAULT
)
ORDER BY db2.UserName;
 
DECLARE @MissingFromDB1 INT = @@ROWCOUNT;
 
PRINT '';
PRINT '----------------------------------------';
PRINT 'Users with Different Types or Schemas';
PRINT '----------------------------------------';
 
-- Find users that exist in both but have different properties
SELECT
    db1.UserName,
    db1.UserType AS DB1_Type,
    db2.UserType AS DB2_Type,
    db1.DefaultSchema AS DB1_Schema,
    db2.DefaultSchema AS DB2_Schema
FROM #DB1Users db1
INNER JOIN #DB2Users db2 ON db1.UserName = db2.UserName
WHERE db1.UserType <> db2.UserType
   OR ISNULL(db1.DefaultSchema, '') <> ISNULL(db2.DefaultSchema, '')
ORDER BY db1.UserName;
 
PRINT '';
PRINT '========================================';
PRINT 'Summary Statistics';
PRINT '========================================';
 
SELECT
    @Database1 AS Database_Name,
    COUNT(*) AS Total_Users
FROM #DB1Users
UNION ALL
SELECT
    @Database2 AS Database_Name,
    COUNT(*) AS Total_Users
FROM #DB2Users;
 
PRINT '';
PRINT '========================================';
PRINT 'DIAGNOSTIC: All Users Side-by-Side';
PRINT '========================================';
 
-- Show all users from both databases for manual review
SELECT
    COALESCE(db1.UserName, db2.UserName) AS UserName,
    CASE
        WHEN db1.UserName IS NOT NULL AND db2.UserName IS NOT NULL THEN 'In Both'
        WHEN db1.UserName IS NOT NULL THEN 'Only in ' + @Database1
        WHEN db2.UserName IS NOT NULL THEN 'Only in ' + @Database2
    END AS Location,
    db1.UserType AS DB1_Type,
    db2.UserType AS DB2_Type,
    db1.DefaultSchema AS DB1_Schema,
    db2.DefaultSchema AS DB2_Schema
FROM #DB1Users db1
FULL OUTER JOIN #DB2Users db2 ON db1.UserName = db2.UserName COLLATE DATABASE_DEFAULT
ORDER BY
    CASE
        WHEN db1.UserName IS NOT NULL AND db2.UserName IS NOT NULL THEN 2
        ELSE 1
    END,
    COALESCE(db1.UserName, db2.UserName);
 
-- Clean up
DROP TABLE #DB1Users;
DROP TABLE #DB2Users;
 
PRINT '';
PRINT 'Comparison Complete!';