SCRIPT: Migrate all database users from one database to another

 -- SQL Server User Migration Script
-- Migrates all users and permissions from source to destination database
-- WARNING: This will DROP all users in the destination database first
 
-- =============================================
-- CONFIGURATION - Set these variables
-- =============================================
DECLARE @SourceDB NVARCHAR(128) = 'sourcedb'
DECLARE @DestDB NVARCHAR(128) = 'destdb'
 
-- =============================================
-- STEP 1: Drop all users in destination database
-- =============================================
DECLARE @DropSQL NVARCHAR(MAX) = ''
DECLARE @SQL NVARCHAR(MAX) = ''
 
-- Switch to destination database to get list of users to drop
SET @SQL = 'USE [' + @DestDB + '];
SELECT @DropSQL = @DropSQL +
    ''USE [' + @DestDB + ']; DROP USER IF EXISTS ['' + name + ''];'' + CHAR(13)
FROM sys.database_principals
WHERE type IN (''S'', ''U'', ''G'')  -- SQL user, Windows user, Windows group
    AND name NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
    AND principal_id > 4'
 
EXEC sp_executesql @SQL, N'@DropSQL NVARCHAR(MAX) OUTPUT', @DropSQL OUTPUT
 
PRINT '-- Dropping existing users in destination database --'
PRINT @DropSQL
EXEC sp_executesql @DropSQL
 
-- =============================================
-- STEP 2: Generate CREATE USER statements
-- =============================================
DECLARE @CreateUserSQL NVARCHAR(MAX) = ''
 
-- Switch to source database to get user information
SET @SQL = 'USE [' + @SourceDB + '];
SELECT @CreateUserSQL = @CreateUserSQL +
    ''USE [' + @DestDB + ']; CREATE USER ['' + dp.name + ''] '' +
    CASE
        WHEN dp.type = ''S'' AND sp.name IS NOT NULL
            THEN ''FOR LOGIN ['' + sp.name + '']''
        WHEN dp.type = ''S'' AND sp.name IS NULL
            THEN ''WITHOUT LOGIN''
        WHEN dp.type IN (''U'', ''G'') AND sp.name IS NOT NULL
            THEN ''FOR LOGIN ['' + sp.name + '']''
        ELSE ''WITHOUT LOGIN''
    END +
    CASE WHEN dp.default_schema_name IS NOT NULL
        THEN '' WITH DEFAULT_SCHEMA = ['' + dp.default_schema_name + '']''
        ELSE ''''
    END + '';'' + CHAR(13)
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN (''S'', ''U'', ''G'')
    AND dp.name NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
    AND dp.principal_id > 4'
 
EXEC sp_executesql @SQL, N'@CreateUserSQL NVARCHAR(MAX) OUTPUT, @DestDB NVARCHAR(128)',
    @CreateUserSQL OUTPUT, @DestDB
 
PRINT ''
PRINT '-- Creating users in destination database --'
PRINT @CreateUserSQL
EXEC sp_executesql @CreateUserSQL
 
-- =============================================
-- STEP 3: Generate role membership statements
-- =============================================
DECLARE @RoleSQL NVARCHAR(MAX) = ''
 
-- Switch to source database to get role memberships
SET @SQL = 'USE [' + @SourceDB + '];
SELECT @RoleSQL = @RoleSQL +
    ''USE [' + @DestDB + ']; ALTER ROLE ['' + r.name + ''] ADD MEMBER ['' + m.name + ''];'' + CHAR(13)
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE m.name NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
    AND m.principal_id > 4'
 
EXEC sp_executesql @SQL, N'@RoleSQL NVARCHAR(MAX) OUTPUT, @DestDB NVARCHAR(128)',
    @RoleSQL OUTPUT, @DestDB
 
PRINT ''
PRINT '-- Assigning role memberships --'
PRINT @RoleSQL
EXEC sp_executesql @RoleSQL
 
-- =============================================
-- STEP 4: Generate database-level permissions
-- =============================================
DECLARE @PermSQL NVARCHAR(MAX) = ''
 
-- Switch to source database to get database-level permissions
SET @SQL = 'USE [' + @SourceDB + '];
SELECT @PermSQL = @PermSQL +
    ''USE [' + @DestDB + ']; '' +
    CASE
        WHEN perm.state_desc = ''GRANT_WITH_GRANT_OPTION''
            THEN ''GRANT ''
        ELSE perm.state_desc + '' ''
    END +
    perm.permission_name COLLATE DATABASE_DEFAULT + '' TO ['' +
    USER_NAME(perm.grantee_principal_id) + '']'' +
    CASE
        WHEN perm.state_desc = ''GRANT_WITH_GRANT_OPTION''
            THEN '' WITH GRANT OPTION''
        ELSE ''''
    END + '';'' + CHAR(13)
FROM sys.database_permissions perm
WHERE perm.major_id = 0
    AND USER_NAME(perm.grantee_principal_id) NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''public'')
    AND perm.grantee_principal_id > 4'
 
EXEC sp_executesql @SQL, N'@PermSQL NVARCHAR(MAX) OUTPUT, @DestDB NVARCHAR(128)',
    @PermSQL OUTPUT, @DestDB
 
PRINT ''
PRINT '-- Granting database-level permissions --'
PRINT @PermSQL
EXEC sp_executesql @PermSQL
 
-- =============================================
-- STEP 5: Generate object-level permissions (tables, views, procedures, etc.)
-- =============================================
DECLARE @ObjPermSQL NVARCHAR(MAX) = ''
 
-- Switch to source database to get object-level permissions
SET @SQL = 'USE [' + @SourceDB + '];
SELECT @ObjPermSQL = @ObjPermSQL +
    ''USE [' + @DestDB + ']; '' +
    CASE
        WHEN perm.state_desc = ''GRANT_WITH_GRANT_OPTION''
            THEN ''GRANT ''
        ELSE perm.state_desc + '' ''
    END +
    perm.permission_name COLLATE DATABASE_DEFAULT +
    '' ON '' +
    CASE
        WHEN obj.type_desc = ''SQL_STORED_PROCEDURE'' THEN ''OBJECT''
        WHEN obj.type_desc = ''SQL_SCALAR_FUNCTION'' THEN ''OBJECT''
        WHEN obj.type_desc = ''SQL_TABLE_VALUED_FUNCTION'' THEN ''OBJECT''
        WHEN obj.type_desc = ''USER_TABLE'' THEN ''OBJECT''
        WHEN obj.type_desc = ''VIEW'' THEN ''OBJECT''
        ELSE obj.type_desc
    END +
    ''::['' + SCHEMA_NAME(obj.schema_id) + ''].['' + obj.name + '']'' +
    '' TO ['' + USER_NAME(perm.grantee_principal_id) + '']'' +
    CASE
        WHEN perm.state_desc = ''GRANT_WITH_GRANT_OPTION''
            THEN '' WITH GRANT OPTION''
        ELSE ''''
    END + '';'' + CHAR(13)
FROM sys.database_permissions perm
INNER JOIN sys.objects obj ON perm.major_id = obj.object_id
WHERE USER_NAME(perm.grantee_principal_id) NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''public'')
    AND perm.grantee_principal_id > 4'
 
EXEC sp_executesql @SQL, N'@ObjPermSQL NVARCHAR(MAX) OUTPUT, @DestDB NVARCHAR(128)',
    @ObjPermSQL OUTPUT, @DestDB
 
PRINT ''
PRINT '-- Granting object-level permissions --'
PRINT @ObjPermSQL
EXEC sp_executesql @ObjPermSQL
 
-- =============================================
-- STEP 6: Generate schema permissions
-- =============================================
DECLARE @SchemaPermSQL NVARCHAR(MAX) = ''
 
-- Switch to source database to get schema permissions
SET @SQL = 'USE [' + @SourceDB + '];
SELECT @SchemaPermSQL = @SchemaPermSQL +
    ''USE [' + @DestDB + ']; '' +
    CASE
        WHEN perm.state_desc = ''GRANT_WITH_GRANT_OPTION''
            THEN ''GRANT ''
        ELSE perm.state_desc + '' ''
    END +
    perm.permission_name COLLATE DATABASE_DEFAULT +
    '' ON SCHEMA::['' + SCHEMA_NAME(perm.major_id) + '']'' +
    '' TO ['' + USER_NAME(perm.grantee_principal_id) + '']'' +
    CASE
        WHEN perm.state_desc = ''GRANT_WITH_GRANT_OPTION''
            THEN '' WITH GRANT OPTION''
        ELSE ''''
    END + '';'' + CHAR(13)
FROM sys.database_permissions perm
WHERE perm.class = 3  -- Schema
    AND USER_NAME(perm.grantee_principal_id) NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''public'')
    AND perm.grantee_principal_id > 4'
 
EXEC sp_executesql @SQL, N'@SchemaPermSQL NVARCHAR(MAX) OUTPUT, @DestDB NVARCHAR(128)',
    @SchemaPermSQL OUTPUT, @DestDB
 
PRINT ''
PRINT '-- Granting schema permissions --'
PRINT @SchemaPermSQL
EXEC sp_executesql @SchemaPermSQL
 
PRINT ''
PRINT '-- User migration completed successfully --'