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(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 = 'TuoNomeUtente';

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');