Risolvere i problemi relativi agli utenti isolati (SQL Server)Troubleshoot Orphaned Users (SQL Server)

In questo argomento si applica a: SìSQL ServerSìDatabase SQL di AzureSìAzure SQL Data Warehouse Sì Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

In SQL ServerSQL Server gli utenti isolati (orfani) appaiono quando un utente del database dipende da un account di accesso nel database master , ma tale account di accesso non esiste più in master.Orphaned users in SQL ServerSQL Server occur when a database user is based on a login in the master database, but the login no longer exists in 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.This can occur when the login is deleted, or when the database is moved to another server where the login does not exist. In questo argomento viene descritto come trovare gli utenti isolati e come riassociarli agli account di accesso.This topic describes how to find orphaned users, and remap them to logins.

Nota

Per ridurre la creazione di utenti isolati, definire utenti del database indipendente per i database che potrebbero essere spostati.Reduce the possibility of orphaned users by using contained database users for databases that might be moved. Per altre informazioni, vedere Utenti di database indipendente: rendere portabile un database.For more information, see Contained Database Users - Making Your Database Portable.

BackgroundBackground

Per connettersi a un database in un'istanza di SQL ServerSQL 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 .To connect to a database on an instance of SQL ServerSQL Server using a security principal (database user identity) based on a login, the principal must have a valid login in the master database. 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 ServerSQL Server.This login is used in the authentication process that verifies the principals identity and determines if the principal is allowed to connect to the instance of SQL ServerSQL Server. Gli account di accesso di SQL ServerSQL Server in un'istanza del server sono riportati nella vista del catalogo sys.server_principals e nella vista di compatibilità sys.sql_logins .The SQL ServerSQL Server logins on a server instance are visible in the sys.server_principals catalog view and the sys.sql_logins compatibility view.

SQL ServerSQL Server consentono di accedere a database singoli come "utente database", che viene mappato all'account di accesso di SQL ServerSQL Server . logins access individual databases as "database user" that is mapped to the SQL ServerSQL Server login. Sono previste tre eccezioni a questa regola:There are three exceptions to this rule:

  • Utenti del database indipendenteContained database users

    Gli utenti del database indipendente eseguono l'autenticazione a livello di database utente e non sono associati agli account di accesso.Contained database users authenticate at the user-database level and are not associated with logins. Questo approccio è consigliato perché facilita lo spostamento dei database. Inoltre gli utenti del database indipendente non possono diventare utenti isolati.This is recommended because the databases are more portable and contained database users cannot become orphaned. Tuttavia, tali utenti vanno ricreati per ogni database.However they must be recreated for each database. Ciò può risultare poco pratico in un ambiente con molti database.This might be impractical in an environment with many databases.

  • Account guest .The guest account.

    Se attivato nel database, consente agli account di accesso di SQL ServerSQL Server non mappati su un utente del database di accedere al database come utenti guest .When enabled in the database, this account permits SQL ServerSQL Server logins that are not mapped to a database user to enter the database as the guest user. L'account guest è disattivato per impostazione predefinita.The guest account is disabled by default.

  • Appartenenza ai gruppi di Microsoft Windows.Microsoft Windows group memberships.

    Un account di accesso di SQL ServerSQL 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.A SQL ServerSQL Server login created from a Windows user can enter a database if the Windows user is a member of a Windows group that is also a user in the database.

    Le informazioni relative al mapping di un account di accesso di SQL ServerSQL Server a un utente del database sono archiviate all'interno del database.Information about the mapping of a SQL ServerSQL Server login to a database user is stored within the database. Includono il nome dell'utente del database e il SID dell'account di accesso di SQL ServerSQL Server corrispondente.It includes the name of the database user and the SID of the corresponding SQL ServerSQL Server login. Le autorizzazioni di tale utente del database vengono applicate come autorizzazioni nel database.The permissions of this database user are applied for authorization in the database.

    Un utente del database (basato su un account di accesso) il cui account di accesso di SQL ServerSQL Server corrispondente non è definito o è definito in modo errato in un'istanza del server non potrà accedere a tale istanza.A database user (based on a login) for which the corresponding SQL ServerSQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Questo utente viene definito utente orfano del database nell'istanza del server.Such a user is said to be an orphaned user of the database on that server instance. È possibile che si verifichi l'isolamento se l'utente del database è mappato a un SID di accesso non presente nell'istanza di master .Orphaning can happen if the database user is mapped to a login SID that is not present in the master instance. Un utente del database può diventare isolato dopo il ripristino o il collegamento del database a un'istanza diversa di SQL ServerSQL Server nella quale non è mai stato creato l'account di accesso.A database user can become orphaned after a database is restored or attached to a different instance of SQL ServerSQL Server where the login was never created. Inoltre un utente del database può diventare isolato (orfano) se l'account di accesso di SQL ServerSQL Server corrispondente viene rimosso.A database user can also become orphaned if the corresponding SQL ServerSQL Server login is dropped. Anche se viene ricreato, l'account di accesso avrà un SID diverso, pertanto l'utente del database resterà isolato.Even if the login is recreated, it will have a different SID, so the database user will still be orphaned.

Per rilevare gli utenti isolati (orfani)To Detect Orphaned Users

Per SQL ServerSQL Server e PDWFor SQL ServerSQL Server and PDW

Per rilevare gli utenti isolati (orfani) in SQL ServerSQL Server in base agli account di accesso con autenticazione di SQL ServerSQL Server mancanti, eseguire l'istruzione seguente nel database utente:To detect orphaned users in SQL ServerSQL Server based on missing SQL ServerSQL Server authentication logins, execute the following statement in the user database:

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 authentication_type_desc = 'INSTANCE';  

Nell'output sono elencati gli utenti autenticazione di SQL ServerSQL Server e gli identificatori di sicurezza (SID) corrispondenti disponibili nel database corrente e non collegati ad alcun account di accesso di SQL ServerSQL Server .The output lists the SQL ServerSQL Server authentication users and corresponding security identifiers (SID) in the current database that are not linked to any SQL ServerSQL Server login.

Per il database SQL e SQL Data WarehouseFor SQL Database and SQL Data Warehouse

La tabella sys.server_principals non è disponibile nel database SQL o in SQL Data Warehouse.The sys.server_principals table is not available in SQL Database or SQL Data Warehouse. Identificare gli utenti isolati (orfani) in questi ambienti con i passaggi seguenti:Identify orphaned users in those environments with the following steps:

  1. Connettersi al database master e selezionare i SID degli account di accesso con la query seguente:Connect to the master database and select the SID's for the logins with the following query:

    SELECT sid 
    FROM sys.sql_logins 
    WHERE type = 'S'; 
    
  2. Connettersi al database utente ed esaminare i SID degli utenti nella tabella sys.database_principals tramite la query seguente:Connect to the user database and review the SID's of the users in the sys.database_principals table, by using the following query:

    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 sys.database_principals del database utente sono presenti SID utente privi di SID di accesso corrispondente nella tabella sql_logins del database master.Compare the two lists to determine if there are user SID's in the user database sys.database_principals table which are not matched by login SID's in the master database sql_logins table.

Per risolvere un utente isolato (orfano)To Resolve an Orphaned User

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:In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login, providing the SID of the database user obtained in the previous section:

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.To map an orphaned user to a login which already exists in master, execute the ALTER USER statement in the user database, specifying the login name.

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.When you recreate a missing login, the user can access the database using the password provided. L'utente può quindi modificare la password dell'account di accesso mediante l'istruzione ALTER LOGIN.Then the user can alter the password of the login account by using the ALTER LOGIN statement.

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

Importante

Qualsiasi account di accesso è autorizzato a modificare la propria password.Any login can change it's own password. Tuttavia, solo gli account di accesso con autorizzazione ALTER ANY LOGIN possono modificare la password dell'account di accesso di un altro utente.Only logins with the ALTER ANY LOGIN permission can change the password of another user's login. Solo i membri del ruolo sysadmin possono tuttavia modificare le password dei membri del ruolo sysadmin .However, only members of the sysadmin role can modify passwords of sysadmin role members.

La procedura deprecata sp_change_users_login funziona anche con gli utenti isolati (orfani).The deprecated procedure sp_change_users_login also works with orphaned users. sp_change_users_login non può essere usata con Database SQLSQL Database.sp_change_users_login cannot be used with Database SQLSQL Database.

Vedere ancheSee Also

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