Risolvere i problemi relativi agli utenti isolati (SQL Server)

Articolo
04/03/2023

Si applica a:SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

In SQL Server gli utenti isolati appaiono quando un utente del database dipende da un account di accesso nel database master, ma tale account di accesso non esiste più nel master. Ciò può verificarsi quando viene eliminato l'account di accesso o quando il database viene spostato in un altro server in cui l'accesso non esiste. In questo argomento viene descritto come trovare gli utenti isolati e come riassociarli 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 Utenti di database indipendente: rendere portabile un database.

Background

Per connettersi a un database in un'istanza di SQL Server usando un'entità di sicurezza (identità utente del database) basata su un account di accesso, l'entità deve avere un account di accesso valido nel database master. Tale account di accesso viene usato nel processo di autenticazione, che verifica l'identità dell'entità e determina se è autorizzata 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 account di accesso di SQL server consentono di accedere a database singoli come "utente database", che viene associato all'account di accesso di SQL Server. Sono previste tre eccezioni a questa regola:

Utenti del database indipendente

Gli utenti del database indipendente eseguono l'autenticazione a livello di database utente e non sono associati agli account di accesso. Questo approccio è consigliato perché facilita lo spostamento dei database. Inoltre gli utenti del database indipendente non possono diventare utenti isolati. Tuttavia, tali utenti vanno ricreati per ogni database. Ciò può risultare poco pratico in un ambiente con molti database.
Account guest .

Se abilitato nel database, consente agli account di accesso di SQL Server non associati a un utente del database di accedere al database come utenti guest. L'account guest è disattivato per impostazione predefinita.
Appartenenza ai gruppi di Microsoft Windows.

Un account di accesso di SQL Server creato da un utente di Windows può accedere a un database se tale utente di Windows è membro di un gruppo di Windows a sua volta utente del database.

Le informazioni relative al mapping di un account di accesso di SQL Server a un utente del database sono archiviate all'interno del database. Includono il nome dell'utente del database e il 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) il cui account di accesso di SQL Server corrispondente non è definito o è definito in modo errato in un'istanza del server non potrà accedere a tale istanza. Questo utente viene definito utente orfano del database nell'istanza del server. È possibile che si verifichi l'isolamento se l'utente del database è mappato a un SID di accesso non presente nell'istanza di 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 viene ricreato, l'account di accesso avrà un SID diverso, pertanto l'utente del database resterà isolato.

Rilevare gli utenti isolati

Per SQL Server e PDW

Per rilevare gli utenti isolati (orfani) in SQL Server in base agli account di accesso con 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';

Nell'output sono elencati gli utenti autenticazione di SQL Server e gli identificatori di sicurezza (SID) corrispondenti disponibili nel database corrente e non collegati ad alcun account di accesso di SQL Server.

Per database SQL e Azure Synapse Analytics

La tabella sys.server_principals non è disponibile nel database SQL o in Azure Synapse Analytics. Identificare gli utenti isolati (orfani) in questi ambienti con i passaggi seguenti:

Connettersi al database master e selezionare i SID degli account di accesso con la query seguente:
```
SELECT sid 
FROM sys.sql_logins 
WHERE type = 'S'; 
```

Connettersi al database utente ed esaminare i SID degli utenti nella tabella sys.database_principals tramite 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';

Confrontare i due elenchi per determinare se nella tabella sys.database_principals del database utente sono presenti SID utente privi di SID di accesso corrispondente nella tabella sql_logins del database master.

Risolvere un utente isolato

Nel database master usare l'istruzione CREATE LOGIN con l'opzione SID per ricreare un account di accesso mancante, fornendo il SID dell'utente 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 isolato (orfano) a un account di accesso già esistente in master, eseguire l'istruzione ALTER USER , 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 mediante 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 .

Vedi anche

CREATE LOGIN (Transact-SQL)
ALTER USER (Transact-SQL)
CREATE USER (Transact-SQL)
sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)
sp_change_users_login (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_grantlogin (Transact-SQL)
sp_password (Transact-SQL)
sys.sysusers (Transact-SQL)
sys.sql_logins sys.syslogins (Transact-SQL)