Determinare le autorizzazioni valide per il motore di databaseDetermining Effective Database Engine Permissions

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2008)sìDatabase SQL di AzuresìAzure SQL Data Warehouse sìParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Questo articolo descrive come determinare chi ha le autorizzazioni per i vari oggetti nel motore di database di SQL Server.This topic describes how to determine who has permissions to various objects in the SQL Server Database Engine. SQL Server implementa due sistemi di autorizzazione per il motore di database.SQL Server implements two permission systems for the Database Engine. Nel sistema precedente basato sui ruoli predefiniti esistono autorizzazioni preconfigurate.An older system of fixed roles has preconfigured permissions. A partire da SQL Server 2005 è disponibile un sistema più flessibile e preciso.Beginning with SQL Server 2005 a more flexible and precise system is available. Le informazioni in questo argomento si applicano a SQL Server a partire dalla versione 2005.(The information in this topic applies to SQL Server, beginning with 2005. Alcuni tipi di autorizzazioni non sono disponibili in alcune versioni di SQL Server.Some types of permissions are not available in some versions of SQL Server.)

Importante

  • Le autorizzazioni valide sono il risultato dell'aggregazione di entrambi i sistemi di autorizzazione.The effective permissions are the aggregate of both permission systems.
  • La negazione di autorizzazioni è prioritaria rispetto alla concessione di autorizzazioni.A denial of permissions overrides a grant of permissions.
  • Se un utente è un membro del ruolo predefinito del server sysadmin, è possibile che le autorizzazioni non vengono controllate e quindi che le negazioni non vengano applicate.If a user is a member of the sysadmin fixed server role, permissions are not checked further, so denials will not be enforced.
  • Il sistema precedente e quello nuovo hanno alcune analogie.The old system and new system have similarities. Ad esempio, l'appartenenza al ruolo predefinito del server sysadmin è simile alla disponibilità dell'autorizzazione CONTROL SERVER.For example, membership in the sysadmin fixed server role is similar to having CONTROL SERVER permission. Ma i sistemi non sono identici.But the systems are not identical. Ad esempio, se un account di accesso ha solo l'autorizzazione CONTROL SERVER e una stored procedure controlla l'appartenenza al ruolo predefinito del server sysadmin, il controllo delle autorizzazioni avrà esito negativo.For example, if a login only has the CONTROL SERVER permission, and a stored procedures checks for membership in the sysadmin fixed server role, then the permission check will fail. È anche vero il contrario.The reverse is also true.

RiepilogoSummary

  • Le autorizzazioni a livello di server possono derivare dall'appartenenza ai ruoli predefiniti del server o ai ruoli del server definiti dall'utente.Server-level permission can come from membership in the fixed server roles or user-defined server roles. Tutti gli utenti appartengono al ruolo predefinito del server public e ricevono tutte le autorizzazioni assegnate a tale ruolo.Everyone belongs to the public fixed server role and receives any permission assigned there.
  • Le autorizzazioni a livello di server possono derivare dalle autorizzazioni concesse agli account di accesso o ai ruoli del server definiti dall'utente.Server-level permissions can come from permission grants to logins or user-defined server roles.
  • Le autorizzazioni a livello di database possono derivare dall'appartenenza ai ruoli predefiniti del database o ai ruoli del database definiti dall'utente.Database-level permission can come from membership in the fixed database roles or user-defined database roles in each database. Tutti gli utenti appartengono al ruolo predefinito del database public e ricevono tutte le autorizzazioni assegnate a tale ruolo.Everyone belongs to the public fixed database role and receives any permission assigned there.
  • Le autorizzazioni a livello di database possono derivare dalle autorizzazioni concesse agli utenti o ai ruoli del database definiti dagli utenti in ogni database.Database-level permissions can come from permission grants to users or user-defined database roles in each database.
  • Le autorizzazioni possono essere ricevute dall'account di accesso guest o dall'utente di database guest, se abilitato.Permissions can be received from the guest login or guest database user if enabled. L'account di accesso guest e gli utenti sono disabilitati per impostazione predefinita.The guest login and users are disabled by default.
  • Gli utenti di Windows possono essere membri dei gruppi di Windows che possono disporre di account di accesso.Windows users can be members of Windows groups which can have logins. SQL Server viene a conoscenza dell'appartenenza ai gruppi di Windows quando un utente di Windows si connette e presenta un token di Windows con l'identificatore di sicurezza di un gruppo di Windows.SQL Server learns of Windows group membership when a Windows user connects and presents a Windows token with the security identifier of a Windows group. Dato che SQL Server non gestisce o riceve gli aggiornamenti automatici sulle appartenenze ai gruppi di Windows, SQL Server non può fare riferimento in modo affidabile alle autorizzazioni degli utenti di Windows ricevute tramite l'appartenenza a gruppi di Windows.Because SQL Server does not manage or receive automatic updates about Windows group memberships, SQL Server cannot reliably report the permissions of Windows users that are received from Windows group membership.
  • Le autorizzazioni possono essere acquisite passando a un ruolo applicazione e fornendo la password.Permissions can be acquired by switching to an application role and providing the password.
  • Le autorizzazioni possono essere acquisite eseguendo una stored procedure che include la clausola EXECUTE AS.Permissions can be acquired by executing a stored procedure that includes the EXECUTE AS clause.
  • Le autorizzazioni possono essere acquisite da account di accesso o utenti con l'autorizzazione IMPERSONATE.Permissions can be acquired by logins or users with the IMPERSONATE permission.
  • I membri del gruppo di amministratori locali del computer possono sempre elevare i propri privilegi a sysadmin.Members of the local computer administrator group can always elevate their privileges to sysadmin. (Non si applica a database SQL.)(Does not apply to SQL Database.)
  • I membri del ruolo predefinito del server securityadmin possono elevare molti dei propri privilegi e in alcuni casi possono elevare i privilegi a sysadmin.Members of the securityadmin fixed server role can elevate many of their privileges and in some cases can elevate the privileges to sysadmin. (Non si applica a database SQL.)(Does not apply to SQL Database.)
  • Gli amministratori di SQL Server possono visualizzare informazioni su tutti gli account di accesso e gli utenti.SQL Server adminstrators can see information about all logins and users. Gli utenti con privilegi inferiori in genere possono visualizzare informazioni solo sulle proprie identità.Less priviledged users usually see information about only their own identities.

Sistema di autorizzazione precedente con ruoli predefinitiOlder Fixed Role Permission System

I ruoli predefiniti del server e del database hanno autorizzazioni preconfigurate non modificabili.Fixed Server Roles and Fixed Database Roles have preconfigured permissions that cannot be changed. Per determinare i membri di un ruolo predefinito del server, eseguire la query seguente.To determine who is a member of a fixed server role, execute the following query.

Nota

Non si applica a database SQL o SQL Data Warehouse per cui non sono disponibili autorizzazioni a livello di server.Does not apply to SQL Database or SQL Data Warehouse where server level permission are not available. La colonna is_fixed_role di sys.server_principals è stata aggiunta in SQL Server 2012.The is_fixed_role column of sys.server_principals was added in SQL Server 2012. Non è necessaria per le versioni precedenti di SQL Server.It is not needed for older versions of SQL Server.

SELECT SP1.name AS ServerRoleName, 
 isnull (SP2.name, 'No members') AS LoginName   
 FROM sys.server_role_members AS SRM
 RIGHT OUTER JOIN sys.server_principals AS SP1
   ON SRM.role_principal_id = SP1.principal_id
 LEFT OUTER JOIN sys.server_principals AS SP2
   ON SRM.member_principal_id = SP2.principal_id
 WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
 ORDER BY SP1.name;

Nota

  • Tutti gli account di accesso sono membri del ruolo public e non possono essere rimossi.All logins are members of the public role and cannot be removed.
  • Questa query controlla le tabelle nel database master, ma può essere eseguita in qualsiasi database per il prodotto locale.This query checks tables in the master database but it can be executed in any database for the on premises product.

Per determinare i membri di un ruolo predefinito del database, eseguire la query seguente in ogni database.To determine who is a member of a fixed database role, execute the following query in each database.

SELECT DP1.name AS DatabaseRoleName, 
   isnull (DP2.name, 'No members') AS DatabaseUserName 
 FROM sys.database_role_members AS DRM
 RIGHT OUTER JOIN sys.database_principals AS DP1
   ON DRM.role_principal_id = DP1.principal_id
 LEFT OUTER JOIN sys.database_principals AS DP2
   ON DRM.member_principal_id = DP2.principal_id
 WHERE DP1.is_fixed_role = 1
 ORDER BY DP1.name;

Per informazioni sulle autorizzazioni concesse a ogni ruolo, vedere le descrizioni dei ruoli nelle illustrazioni nella documentazione in linea (Ruoli a livello di server e Ruoli a livello di database).To understand the permissions that are granted to each role, see the role descriptions at illustrations in Books Online (Server-Level Roles, and Database-Level Roles).

Nuovo sistema di autorizzazioni granulariNewer Granular Permission System

Questo sistema è estremamente flessibile e ciò significa che può diventare complicato se gli utenti che lo configurano vogliono essere molto precisi.This system is extremely flexible, which means it can be complicated if the people setting it up want to be very precise. Non si tratta di un aspetto necessariamente negativo. Ad esempio, è molto importante che gli istituti finanziari siano estremamente precisi.That's not necessarily bad; I hope my financial institution is precise. Per semplificare la configurazione può essere utile creare ruoli, assegnare autorizzazioni ai ruoli e quindi aggiungere gruppi di utenti ai ruoli.To simplify matters it helps to create roles, assign permissions to roles, and then add groups of people to the roles. Si può semplificare ulteriormente il sistema se il team di sviluppo del database separa le attività in base allo schema e quindi concede le autorizzazioni di ruolo per un intero schema anziché per singole tabelle o stored procedure.And it's easier if the database development team separates activity by schema and then grants role permissions to a whole schema instead of to individual tables or procedures. Il mondo reale è però complesso ed è opportuno presupporre che le esigenze aziendali creino requisiti di sicurezza imprevisti.But the real world is complex and we have to assume that business needs create unexpected security requirements.

La figura seguente illustra le autorizzazioni e le relative relazioni.The following graphic shows the permissions and their relationships to each other. Alcune delle autorizzazioni di livello superiore (ad esempio CONTROL SERVER) sono elencate più volte.Some of the higher level permissions (such as CONTROL SERVER) are listed many times. In questo argomento il poster è molto piccolo e non può essere consultato.In this topic, the poster is far to small to read. Fare clic sull'immagine per scaricare il poster relativo alle autorizzazioni del motore di database in formato pdf.Click the image to download the Database Engine Permissions Poster in pdf format.

[Autorizzazioni del motore di database](http://go.microsoft.com/fwlink/?LinkId=229142)(http://go.microsoft.com/fwlink/?LinkId=229142)

Classi di sicurezzaSecurity Classes

Le autorizzazioni possono essere concesse a livello di server, a livello di database, a livello di schema, a livello di oggetto e così via. Esistono 26 livelli (denominati classi).Permissions can be granted at the server-level, the database-level, the schema-level, or the object-level, etc. There are 26 levels (called classes). L'elenco completo delle classi in ordine alfabetico è: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASE SCOPED CREDENTIAL, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION.The complete list of classes in alphabetic order is: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASE SCOPED CREDENTIAL, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. (Alcune classi non sono disponibili per alcuni tipi di SQL Server.) Per ottenere informazioni complete su ogni classe, è necessaria una query diversa.(Some classes are not available on some types of SQL Servers.) To provide full information about each class requires a different query.

EntitàPrincipals

Le autorizzazioni vengono concesse alle entità.Permissions are granted to principals. Le entità possono essere ruoli del server, account di accesso, ruoli del database o utenti.Principals can be server roles, logins, database roles, or users. Gli account di accesso possono rappresentare gruppi di Windows che includono molti utenti di Windows.Logins can represent Windows groups that include many Windows users. Dato che i gruppi di Windows non vengono gestiti in SQL Server, SQL Server non sempre conosce i membri di un gruppo di Windows.Since Windows groups are not maintained by SQL Server, SQL Server does not always know who is a member of a Windows group. Quando un utente di Windows si connette a SQL Server, il pacchetto di accesso contiene i token di appartenenza ai gruppi di Windows per l'utente.When a Windows user connects to SQL Server, the login packet contains the Windows group membership tokens for the user.

Quando un utente di Windows si connette usando un account di accesso basato su un gruppo di Windows, alcune attività potrebbero richiedere la creazione di un account accesso in SQL Server o la rappresentazione dell'utente di Windows.When a Windows user connects using a login based on a Windows group, some activities may require SQL Server to create a login or user to represent the individual Windows user. Si supponga che un gruppo di Windows (Tecnici) contenga gli utenti Maria, Luca ed Enzo e che il gruppo Tecnici abbia un account utente del database.For example, a Windows group (Engineers) contains users (Mary, Todd, Pat) and the Engineers group has a database user account. Se Maria è autorizzata e crea una tabella, potrebbe essere creato un utente (Maria) come proprietario della tabella.If Mary has permission and creates a table, a user (Mary) might be created to be the owner of the table. Se a Luca viene negata un'autorizzazione che invece ha il resto del gruppo Tecnici, è necessario creare l'utente Luca per tenere traccia della negazione dell'autorizzazione.Or if Todd is denied a permission that the rest of the Engineers group has, then the user Todd must be created to track the permission denial.

Tenere presente che un utente di Windows potrebbe essere un membro di più di un gruppo di Windows, ad esempio sia Tecnici che Manager.Remember that a Windows user might be a member of more than one Windows group (e.g. both Engineers, and Managers). Per determinare le autorizzazioni valide, verranno aggregate e valutate tutte le autorizzazioni concesse o negate all'account di accesso Tecnici, all'account di accesso Manager, agli utenti singolarmente e ai ruoli di cui sono membri degli utenti.Permissions granted or denied to the Engineers login, to the Managers login, granted or denied to the user individually, and granted or denied to roles that the user is a member of, will all be aggregated and evaluated to for the effective permissions. La funzione HAS_PERMS_BY_NAME consente di scoprire se un utente o un account di accesso ha una particolare autorizzazione.The HAS_PERMS_BY_NAME function can reveal whether a user or login has a particular permission. Non esiste tuttavia un modo ovvio per determinare l'origine della concessione o negazione dell'autorizzazione.However, there is no obvious way of determining the source of the grant or denial of permission. È necessario studiare l'elenco di autorizzazioni e probabilmente sperimentare.You must study the list of permissions and perhaps experiment using trial and error.

Query utiliUseful Queries

Autorizzazioni per il serverServer Permissions

La query seguente restituisce un elenco delle autorizzazioni concesse o negate a livello di server.The following query returns a list of the permissions that have been granted or denied at the server level. Questa query deve essere eseguita nel database master.This query should be executed in the master database.

Nota

Non è possibile concedere autorizzazioni a livello di server o eseguire query per recuperare tali autorizzazioni nel database SQL o in SQL Data Warehouse.Server-level permissions cannot be granted or queried on SQL Database or SQL Data Warehouse.

SELECT pr.type_desc, pr.name, 
 isnull (pe.state_desc, 'No permission statements') AS state_desc, 
 isnull (pe.permission_name, 'No permission statements') AS permission_name 
 FROM sys.server_principals AS pr
 LEFT OUTER JOIN sys.server_permissions AS pe
   ON pr.principal_id = pe.grantee_principal_id
 WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
 ORDER BY pr.name, type_desc;

Autorizzazioni per il databaseDatabase Permissions

La query seguente restituisce un elenco delle autorizzazioni concesse o negate a livello di database.The following query returns a list of the permissions that have been granted or denied at the database level. Questa query deve essere eseguita in ogni database.This query should be executed in each database.

SELECT pr.type_desc, pr.name, 
 isnull (pe.state_desc, 'No permission statements') AS state_desc, 
 isnull (pe.permission_name, 'No permission statements') AS permission_name 
FROM sys.database_principals AS pr
LEFT OUTER JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0 
ORDER BY pr.name, type_desc;

Ogni classe di autorizzazione per la tabella delle autorizzazioni può essere unita in join ad altre viste di sistema che forniscono informazioni correlate su tale classe di entità a protezione diretta.Each class of permission the permission table can be joined to other system views that provide related information about that class of securable. Ad esempio, la query seguente fornisce il nome dell'oggetto di database interessato dall'autorizzazione.For example, the following query provides the name of the database object that is affected by the permission.

SELECT pr.type_desc, pr.name, pe.state_desc, 
 pe.permission_name, s.name + '.' + oj.name AS Object, major_id
 FROM sys.database_principals AS pr
 JOIN sys.database_permissions AS pe
   ON pr.principal_id = pe.grantee_principal_id
 JOIN sys.objects AS oj
   ON oj.object_id = pe.major_id
 JOIN sys.schemas AS s
   ON oj.schema_id = s.schema_id
 WHERE class_desc = 'OBJECT_OR_COLUMN';

Usare la funzione HAS_PERMS_BY_NAME per determinare se un utente specifico (in questo caso TestUser) ha un'autorizzazione.Use the HAS_PERMS_BY_NAME function to determine if a particular user (in this case TestUser) has a permission. Esempio:For example:

EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;

Per informazioni dettagliate sulla sintassi, vedere HAS_PERMS_BY_NAME.For the details of the syntax, see HAS_PERMS_BY_NAME.

Vedere anche:See Also:

Introduzione alle autorizzazioni del motore di database Getting Started with Database Engine Permissions
Esercitazione: Introduzione al motore di databaseTutorial: Getting Started with Database Engine