Ruoli del server del database SQL di Azure per la gestione delle autorizzazioni

Si applica a: database SQL di Azure

Questo articolo descrive i ruoli predefiniti a livello di server nel database SQL di Azure.

Panoramica

Nel database SQL di Azure, il server è un concetto logico e le autorizzazioni non possono essere concesse a livello di server. Per semplificare la gestione delle autorizzazioni, il database SQL di Azure fornisce un set di ruoli predefiniti a livello di server che consentono di gestire le autorizzazioni in un server logico. I ruoli sono entità di sicurezza che raggruppano gli account di accesso.

Per questi ruoli predefiniti speciali a livello di server si usa il prefisso ##MS_ e il suffisso ## per distinguerli da altre entità normali create dall'utente.

Analogamente a SQL Server locale, le autorizzazioni del server sono organizzate gerarchicamente. Le autorizzazioni utilizzate da questi ruoli a livello di server possono essere propagate alle autorizzazioni del database. Affinché le autorizzazioni siano effettivamente utili a livello di database, un account di accesso deve essere un membro del ruolo ##MS_DatabaseConnector## a livello di server, che concede CONNECT a tutti i database o ha un account utente in singoli database. Questo vale anche per il database master virtuale.

Ad esempio, il ruolo ##MS_ServerStateReader## a livello di server contiene l'autorizzazione VIEW SERVER STATE. Se un account di accesso membro di questo ruolo dispone di un account utente nei database master e WideWorldImporters, l'utente dispone dell'autorizzazione VIEW DATABASE STATE in questi due database.

Il database SQL di Azure attualmente fornisce sette ruoli predefiniti del server. Le autorizzazioni concesse ai ruoli predefiniti del server non possono essere modificate e questi ruoli non possono avere altri ruoli predefiniti come membri. Puoi aggiungere account di accesso a livello di server come membri ai ruoli a livello di server.

Per altre informazioni su account di accesso e utenti del database SQL di Azure, vedi Autorizzare l'accesso al database a database SQL, Istanza gestita di SQL e Azure Synapse Analytics.

Ruoli predefiniti a livello di server

Nella tabella seguente vengono illustrati i ruoli predefiniti a livello di server e le relative funzionalità.

Autorizzazioni dei ruoli predefiniti del server

A ogni ruolo predefinito a livello di server vengono assegnate autorizzazioni specifiche. La tabella seguente mostra le autorizzazioni assegnate ai ruoli a livello di server. Mostra inoltre le autorizzazioni a livello di database, che vengono ereditate finché l'utente può connettersi a singoli database.

Autorizzazioni

Solo l'account amministratore del server o l'account amministratore di Microsoft Entra (che può essere un gruppo di Microsoft Entra) può aggiungere altri account di accesso ai ruoli del server o rimuoverli. Questa caratteristica è specifica del database SQL di Azure.

Gestire i ruoli a livello di server

Nella tabella seguente vengono illustrate le viste di sistema e le funzioni che puoi utilizzare per gestire i ruoli a livello di server nel database SQL di Azure.

Esempi

Gli esempi in questa sezione illustrano come gestire i ruoli a livello di server nel database SQL di Azure.

R. Aggiungere un account di accesso SQL a un ruolo a livello di server

Nell'esempio seguente l'account di accesso SQL Jiao viene aggiunto al ruolo a livello di server ##MS_ServerStateReader##. Questa istruzione deve essere eseguita nel database master virtuale.

ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER Jiao;
GO

B. Elencare tutte le entità (autenticazione SQL) che sono membri di un ruolo a livello di server

L'istruzione seguente restituisce tutti i membri di qualsiasi ruolo a livello di server predefinito usando le viste del catalogo sys.server_role_members e sys.sql_logins. Questa istruzione deve essere eseguita nel database master virtuale.

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

C. Esempio completo: aggiungere un account di accesso a un ruolo a livello di server, recuperare i metadati per l'appartenenza ai ruoli e le autorizzazioni ed eseguire una query di test

Parte 1: Preparazione dell'appartenenza ai ruoli e dell'account utente

Esegui questo comando dal database master virtuale.

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER Jiao;

-- check membership in metadata:
SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao');
--> 1 = Yes

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

Il set di risultati è il seguente.

MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName
------------- ------------- ------------------ -----------
6         Jiao      11            ##MS_ServerStateReader##

Esegui questo comando da un database utente.

-- Create a database-User for 'Jiao'
CREATE USER Jiao
FROM LOGIN Jiao;
GO

Parte 2: Test dell'appartenenza ai ruoli

Accedi come account di accesso Jiao ed esegui la connessione al database utente usato nell'esempio.

-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server');

-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##');
--> 1 = Yes

-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE');
--> 1 = Yes

-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

-- example query:
SELECT * FROM sys.dm_exec_query_stats;
--> will return data since this user has the necessary permission

D. Controlla i ruoli a livello di server per gli account di accesso di Microsoft Entra

Esegui questo comando nel database master virtuale per visualizzare tutti gli account di accesso di Microsoft Entra che fanno parte dei ruoli a livello di server in database SQL. Per altre informazioni sugli account di accesso del server di Microsoft Entra, vedi Entità server di Microsoft Entra.

SELECT member.principal_id AS MemberPrincipalID,
    member.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS member
    ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
    -- prevent SQL Logins from interfering with resultset
    SELECT principal_id
    FROM sys.sql_logins AS sql_logins
    WHERE member.principal_id = sql_logins.principal_id
);

E. Controllare i ruoli del database master virtuale per account di accesso specifici

Esegui questo comando nel database master virtuale per verificare i ruoli di cui bob dispone o modificare il valore in modo che corrisponda all'entità.

SELECT DR1.name AS DbRoleName,
    ISNULL(DR2.name, 'No members') AS DbUserName
FROM sys.database_role_members AS DbRMem
RIGHT JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id
LEFT JOIN sys.database_principals AS DR2
    ON DbRMem.member_principal_id = DR2.principal_id
WHERE DR1.type = 'R'
    AND DR2.name LIKE 'bob%';

Limitazioni dei ruoli a livello di server

• Le assegnazioni di ruolo potrebbero richiedere fino a 5 minuti per diventare effettive. Anche per le sessioni esistenti, le modifiche apportate alle assegnazioni di ruolo del server non diventano effettive fino a quando la connessione non viene chiusa e riaperta. Ciò è dovuto all'architettura distribuita tra il database master e gli altri database nello stesso server logico.

  • Soluzione alternativa parziale: per ridurre il periodo di attesa e garantire che le assegnazioni di ruolo del server siano aggiornate in un database, un amministratore del server o un amministratore di Microsoft Entra può eseguire DBCC FLUSHAUTHCACHE nei database utente disponibili per l'account di accesso. Gli utenti attualmente connessi devono comunque riconnettersi dopo l'esecuzione di DBCC FLUSHAUTHCACHE per rendere effettive le modifiche di appartenenza.

• IS_SRVROLEMEMBER() non è supportato nel database master.

Contenuto correlato

• Ruoli a livello di database
• Viste del catalogo relative alla sicurezza (Transact-SQL)
• Funzioni di sicurezza (Transact-SQL)
• Autorizzazioni (Motore di database)
• DBCC FLUSHAUTHCACHE (Transact-SQL)