Gestire gli Utenti Orfani in SQL Server: Una Guida Completa

Gestire gli utenti orfani in SQL Server è fondamentale per la sicurezza e l’amministrazione del database. Gli utenti orfani si verificano quando un utente del database esiste senza un login corrispondente nel database master. Questa discrepanza può interrompere l’accesso e portare a potenziali vulnerabilità di sicurezza. In questo articolo, esploreremo cosa sono gli utenti orfani, come rilevarli e i metodi per risolverli, inclusi esempi e scenari aggiornati.

Contesto

Per connettersi a un database SQL Server, un utente deve avere un login valido nel database master. Questo login autentica l’utente e viene mappato a un utente del database per l’autorizzazione. La mappatura è mantenuta utilizzando il Security Identifier (SID), garantendo che gli utenti del database e i login del server siano collegati correttamente.

Le eccezioni alla regola di mappatura login-utente includono:

1. Utenti del database contenuto: Si autenticano a livello di database, eliminando la dipendenza dai login del server. Questi utenti migliorano la portabilità del database ma richiedono una ricreazione separata per ogni database.
2. Account Ospite: Abilitati per impostazione predefinita, consentono l’accesso a utenti senza mappatura esplicita ma sono generalmente sconsigliati per motivi di sicurezza.
3. Appartenenze ai Gruppi di Microsoft Windows: I membri dei gruppi di Windows possono accedere ai database se il gruppo è aggiunto come utenti.

Un utente del database diventa orfano quando:

• Il login corrispondente viene eliminato.
• Il database viene migrato o ripristinato senza i login associati.
• Il mapping SID tra l’utente e il login è incorretto.

Cos’è un utente orfano?

Un utente orfano si verifica quando:

• L’utente del database esiste, ma il corrispondente login SQL Server nel database master non c’è.
• Il SID dell’utente del database e il SID del login non corrispondono.

Questi scenari interrompono l’autenticazione e l’autorizzazione, portando a errori o accesso limitato.

Rilevare gli utenti orfani

Per SQL Server

Utilizzare la seguente query per identificare gli utenti orfani:

SELECT dp.type_desc, dp.sid, dp.name AS user_name  

FROM sys.database_principals AS dp  

LEFT JOIN sys.server_principals AS sp  

    ON dp.sid = sp.sid  

WHERE sp.sid IS NULL  

    AND dp.authentication_type_desc = 'INSTANCE';

Per Azure SQL Database o Synapse Analytics

1. Recuperare i SIDs dei logins nel database master:

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. Recuperare i SIDs degli utenti nel database di destinazione:

SELECT name, sid, principal_id

FROM sys.database_principals 

WHERE type = 'S' 

  AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')

  AND authentication_type_desc = 'INSTANCE';

3. Confrontare le liste per identificare le discrepanze.

Risolvere gli utenti orfani

1. Ricreare il Login Mancante con SID 

Se il login è stato eliminato:

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. Associare un Utente Orfano a un Login Esistente 

Se il login esiste ma non è mappato:

ALTER USER <user_name> WITH LOGIN = <login_name>;

3. Cambiare la Password dell’Utente 

Dopo aver ricreato il login, aggiornarne la password:

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';

Utilizzo di sp_FindOrphanedUser

La stored procedure sp_FindOrphanedUser è uno strumento potente per rilevare e risolvere gli utenti orfani. Fornisce:

1. Un elenco di utenti orfani.
2. Script generati automaticamente per le riparazioni.
3. Dettagli sui schemi posseduti da utenti orfani.

Ottieni il codice del pacchetto da sp_FindOrphanedUser.

Esecuzione della Procedura

Esecuzione predefinita:

EXEC sp_FindOrphanedUser;

Per un database specifico:

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

Esempi di scenari di utenti orfani

Esempio 1: Utente senza Login

Scenario

Un login viene eliminato, lasciando l’utente del database orfano.

• Rileva l’utente orfano:
  MS SQL

   

  EXEC sp_FindOrphanedUser;

• Ricreare il login:
  MS SQL

  CREA LOGIN [Utente1] CON PASSWORD = 'SecurePassword123';

  USO [EsempioDB];

  MODIFICA UTENTE [Utente1] CON LOGIN = [Utente1];

Esempio 2: Discrepanza SID

Scenario

Il SID di accesso non corrisponde al SID dell’utente.

• Rileva la discrepanza:
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• Correggere il mapping SID:
  MS SQL

  USE [ExampleDB];

  EXEC sp_change_users_login 'UPDATE_ONE', 'User2', 'User2';

Esempio 3: Utente orfano che possiede uno schema

Scenario

L’utente orfano impedisce l’eliminazione dello schema.

• Rileva la proprietà dello schema:
  MS SQL

   

  EXEC sp_FindOrphanedUser @NomeDatabase = 'ExampleDB';

• Riassegna la proprietà dello schema:
  MS SQL

  USE [ExampleDB];

  ALTER AUTHORIZATION ON SCHEMA::[User3Schema] TO [dbo];

  DROP USER [User3];

Best Practices

• Utilizzare Utenti del Database Contenuti: Evitare la dipendenza dagli accessi al server.
• Sincronizzare gli Accessi: Migrare o ripristinare sempre gli accessi durante le migrazioni del database.
• Audit Regolari: Controllare periodicamente la presenza di utenti orfani per mantenere la sicurezza.

Conclusione

Gestire gli utenti orfani garantisce la sicurezza del database e l’efficienza operativa. Utilizzando query e strumenti come sp_FindOrphanedUser, è possibile identificare e risolvere rapidamente i problemi, mantenendo un accesso fluido per gli utenti legittimi. Adottare le migliori pratiche come gli utenti contenuti e audit regolari può prevenire tali problemi in futuro.