MSSQL Esportare Utenze

SSMS

Export

Powershell

Installare dbatools:

Install-Module dbatools -Force

Esegue la copia:

Copy-DbaLogin -Source ServerOrigine -Destination ServerDestinazione

Oppure:

Start-DbaMigration -Source "VecchioServer" -Destination "NuovoServer" -MigrationType Logins

Per migrare anche linked server e job:

Start-DbaInstanceMigration -Source "VecchioServer" -Destination "NuovoServer"

Script TSQL

Singolo utente:

SELECT 'CREATE LOGIN [' + name + '] WITH PASSWORD = ' + 
      MASTER.sys.fn_varbintohexstr(convert(varbinary(max),LOGINPROPERTY(name, 'PasswordHash'))) + ' HASHED, 
      DEFAULT_DATABASE = [' + default_database_name + '];'
FROM sys.server_principals
WHERE type = 'S' -- Tipo 'S' per utenti SQL, usa 'U' per utenti Windows
AND name = 'NomeUtente';

Tutti gli utenti:

 SET NOCOUNT ON;

-- 1. Genera la creazione degli Utenti (mappati sui Login)
SELECT 'CREATE USER [' + dp.name + '] FOR LOGIN [' + l.name + ']' 
       + CASE WHEN dp.default_schema_name IS NOT NULL THEN ' WITH DEFAULT_SCHEMA=[' + dp.default_schema_name + '];' ELSE ';' END AS [-- T-SQL Script]
FROM sys.database_principals dp
INNER JOIN sys.server_principals l ON dp.sid = l.sid
WHERE dp.type IN ('S', 'U', 'G') AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')

UNION ALL

-- 2. Genera l'associazione ai Ruoli del Database (es. db_owner, db_datareader)
SELECT 'ALTER ROLE [' + role_dp.name + '] ADD MEMBER [' + member_dp.name + '];'
FROM sys.database_role_members drm
INNER JOIN sys.database_principals role_dp ON drm.role_principal_id = role_dp.principal_id
INNER JOIN sys.database_principals member_dp ON drm.member_principal_id = member_dp.principal_id
WHERE member_dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')

UNION ALL

-- 3. Genera i Privilegi espliciti sugli oggetti (GRANT SELECT, EXECUTE, ecc.)
SELECT 
    dp.state_desc + ' ' + dp.permission_name + ' ON [' + 
    s.name + '].[' + o.name + '] TO [' + p.name + '];'
FROM sys.database_permissions dp
INNER JOIN sys.objects o ON dp.major_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals p ON dp.grantee_principal_id = p.principal_id
WHERE p.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');

Utenti Orfani

EXEC sp_MSforeachdb '
USE [?];
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
    DECLARE @username NVARCHAR(255);
    DECLARE user_cursor CURSOR FOR 
    SELECT dp.name 
    FROM sys.database_principals dp
    LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
    WHERE dp.type IN (''S'', ''U'') 
      AND sp.sid IS NULL 
      AND dp.name NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'');

    OPEN user_cursor;
    FETCH NEXT FROM user_cursor INTO @username;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Tenta di rimappare l''utente al login omonimo sul nuovo server
        IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @username)
        BEGIN
            PRINT ''Riappeso utente orfano: '' + @username + '' nel database [?]'';
            EXEC (''ALTER USER ['' + @username + ''] WITH LOGIN = ['' + @username + '']'');
        END
        FETCH NEXT FROM user_cursor INTO @username;
    END

    CLOSE user_cursor;
    DEALLOCATE user_cursor;
END
';