Condividi tramite

Risolvere i problemi relativi agli utenti isolati (SQL Server)

Si applica a: SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)

Gli utenti diventano orfani in SQL Server quando un utente del database è associato a un login nel database master, ma il login non esiste più in master. Ciò può verificarsi quando l'account di accesso viene eliminato o quando il database viene spostato in un altro server in cui l'account di accesso non esiste. Questo articolo descrive come trovare gli utenti orfani ed eseguirne di nuovo il mapping agli account di accesso.

Nota

Per ridurre la creazione di utenti isolati, definire utenti del database indipendente per i database che potrebbero essere spostati. Per altre informazioni, vedere Rendere il database portabile utilizzando database indipendenti.

Background

Per le connessioni a un database in un'istanza di SQL Server che usano un'entità di sicurezza (identità utente del database) in base a un account di accesso, l'entità deve disporre di un account di accesso valido nel master database. Questo login viene usato nel processo di autenticazione che verifica l'identità dell'utente e determina se l'utente è autorizzato a connettersi all'istanza di SQL Server. Gli account di accesso di SQL Server in un'istanza del server sono riportati nella vista del catalogo sys.server_principals e nella vista di compatibilità sys.sql_logins.

Gli accessi di SQL Server accedono ai singoli database come "utenti del database" associati all'accesso di SQL Server. Sono previste tre eccezioni a questa regola:

  • Utenti del database indipendente

    Gli utenti di database contenuti eseguono l'autenticazione al livello di utente-database e non sono associati agli account di accesso. Questo modello è consigliato perché i database sono più portabili e gli utenti di database contenuti non possono rimanere orfani. Tuttavia, devono essere ricreati per ogni database. Questo modello potrebbe non essere pratico in un ambiente con molti database.

  • Account guest

    Se abilitato in un database, questo account consente agli account di accesso di SQL Server non mappati a un utente del database di accedere al database come utente guest . L'account guest è disattivato per impostazione predefinita.

  • Appartenenze ai gruppi di Microsoft Windows

    Un account di accesso di SQL Server creato da un utente di Windows può accedere a un database se l'utente di Windows è membro di un gruppo di Windows che è anche un utente del database.

Le informazioni sulla mappatura di un login di SQL Server a un utente del database vengono archiviate nel database. Include il nome dell'utente del database e l'identificatore di sicurezza (SID) dell'account di accesso di SQL Server corrispondente. Le autorizzazioni di tale utente del database vengono applicate come autorizzazioni nel database.

Un utente del database (basato su un account di accesso) per il quale l'account di accesso di SQL Server corrispondente non è definito o non è definito correttamente in un'istanza del server non può accedere all'istanza. Questo utente viene definito utente orfano del database nell'istanza del server. Il problema di disconnessione può verificarsi se l'utente del database è mappato a un login SID non presente nel database master. Un utente del database può diventare isolato dopo il ripristino o il collegamento del database a un'istanza diversa di SQL Server nella quale non è mai stato creato l'account di accesso. Inoltre un utente del database può diventare isolato se l'account di accesso di SQL Server corrispondente viene rimosso. Anche se l'account di accesso viene ricreato, avrà un valore diverso SID, in modo che l'utente del database sia ancora orfano.

Rilevare gli utenti orfani

Per SQL Server e PDW

Per rilevare gli utenti orfani in SQL Server in base agli account di accesso di autenticazione di SQL Server mancanti, eseguire l'istruzione seguente nel database utente:

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

L'output elenca gli utenti di autenticazione di SQL Server e i SID corrispondenti nel database corrente che non sono collegati ad alcun account di accesso di SQL Server.

Per il database SQL di Azure e Azure Synapse Analytics

La tabella sys.server_principals non è disponibile nel database SQL o in Azure Synapse Analytics. Identificare gli utenti orfani in tali ambienti completando questi passaggi:

  1. Connettersi al master database e selezionare i SID per gli account di accesso usando la query seguente:

    SELECT sid
FROM sys.sql_logins
WHERE type = 'S';

  2. Connettersi al database utente e esaminare i SID degli utenti nella sys.database_principals tabella utilizzando la query seguente:

    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 i due elenchi per determinare se nella tabella del database sys.database_principals utente sono presenti SID utente che non corrispondono ai SID di accesso nella tabella di master database sql_logins .

Risolvere un utente orfano

master Nel database usare l'istruzione CREATE LOGIN con l'opzione SID per ricreare un account di accesso mancante. Fornire il SID dell'utente del database ottenuto nella sezione precedente.

CREATE LOGIN <login_name>
WITH PASSWORD = '<use_a_strong_password_here>',
SID = <SID>;

Per eseguire il mapping di un utente orfano a un account di accesso già esistente in master, eseguire l'istruzione ALTER USER nel database utente, specificando il nome dell'account di accesso:

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

Quando si ricrea un account di accesso mancante, l'utente può accedere al database usando la password specificata. L'utente può quindi modificare la password dell'account di accesso usando l'istruzione ALTER LOGIN :

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

Importante

Qualsiasi account di accesso è autorizzato a modificare la propria password. Tuttavia, solo gli account di accesso con autorizzazione ALTER ANY LOGIN possono modificare la password dell'account di accesso di un altro utente. Solo i membri del ruolo sysadmin possono tuttavia modificare le password dei membri del ruolo sysadmin .

Contenuti correlati

  • Last updated on