ALTER AUTHORIZATION (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Change la propriété d'un élément sécurisable.

Conventions de la syntaxe Transact-SQL

Syntaxe

-- Syntax for SQL Server
ALTER AUTHORIZATION
    ON [ <class_type>:: ] entity_name
    TO { principal_name | SCHEMA OWNER }
    [;]

<class_type> ::=
     {
      OBJECT | ASSEMBLY | ASYMMETRIC KEY | AVAILABILITY GROUP | CERTIFICATE
    | CONTRACT | TYPE | DATABASE | ENDPOINT | FULLTEXT CATALOG
    | FULLTEXT STOPLIST | MESSAGE TYPE | REMOTE SERVICE BINDING
    | ROLE | ROUTE | SCHEMA | SEARCH PROPERTY LIST | SERVER ROLE
    | SERVICE | SYMMETRIC KEY | XML SCHEMA COLLECTION
     }
-- Syntax for SQL Database

ALTER AUTHORIZATION
    ON [ <class_type>:: ] entity_name
    TO { principal_name | SCHEMA OWNER }
    [;]

<class_type> ::=
     {
    OBJECT | ASSEMBLY | ASYMMETRIC KEY | CERTIFICATE
     | TYPE | DATABASE | FULLTEXT CATALOG
     | FULLTEXT STOPLIST
     | ROLE | SCHEMA | SEARCH PROPERTY LIST
     | SYMMETRIC KEY | XML SCHEMA COLLECTION
     }
-- Syntax for Azure Synapse Analytics

ALTER AUTHORIZATION ON
     [ <class_type> :: ] <entity_name>
     TO { principal_name | SCHEMA OWNER }
    [;]

    <class_type> ::= {
    SCHEMA
     | OBJECT
    }

    <entity_name> ::=
    {
    schema_name
     | [ schema_name. ] object_name
    }
-- Syntax for Parallel Data Warehouse

ALTER AUTHORIZATION ON
     [ <class_type> :: ] <entity_name>
     TO { principal_name | SCHEMA OWNER }
    [;]

<class_type> ::= {
    DATABASE
     | SCHEMA
     | OBJECT
    }

<entity_name> ::=
    {
    database_name
     | schema_name
     | [ schema_name. ] object_name
    }

Notes

Cette syntaxe n’est pas prise en charge par le pool SQL serverless dans Azure Synapse Analytics.

Notes

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 et versions antérieures, consultez Versions antérieures de la documentation.

Arguments

<class_type> Classe sécurisable de l’entité pour laquelle il y a un changement de propriétaire. OBJECT est la valeur par défaut.

Classe Produit
OBJECT S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW).
ASSEMBLY S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database.
ASYMMETRIC KEY S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database.
AVAILABILITY GROUP S’applique à : SQL Server 2012 et versions ultérieure.
CERTIFICATE S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database.
CONTRACT S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
DATABASE S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database. Pour plus d’informations, consultez ALTER AUTHORIZATION pour les bases de données.
ENDPOINT S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
FULLTEXT CATALOG S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database.
FULLTEXT STOPLIST S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database.
MESSAGE TYPE S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
REMOTE SERVICE BINDING S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
ROLE S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database.
ROUTE S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
SCHEMA S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW).
SEARCH PROPERTY LIST S’applique à : SQL Server 2012 (11.x) et versions ultérieures, Azure SQL Database.
SERVER ROLE S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
SERVICE S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
SYMMETRIC KEY S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database.
TYPE S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database.
XML SCHEMA COLLECTION S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database.

entity_name Nom de l’entité.

principal_name | SCHEMA OWNER Nom du principal de sécurité qui possède l’entité. Les objets de bases de données doivent appartenir à un principal de base de données, un utilisateur de base de données ou un rôle. Les objets serveur (par exemple les bases de données) doivent appartenir à un principal serveur (un identifiant de connexion). Spécifiez SCHEMA OWNER comme *principal_name- pour indiquer que l’objet doit appartenir au principal qui est propriétaire du schéma de l’objet.

Notes

ALTER AUTHORIZATION peut s'utiliser pour modifier la propriété d'une entité qui a un propriétaire. La propriété d'entités contenues dans une base de données peut être transférée à n'importe quel principal de niveau base de données. La propriété d'entités de niveau serveur peut être transférée à n'importe quel principal de niveau serveur.

Important

À partir de SQL Server 2005 (9.x), un utilisateur peut être propriétaire d'un OBJECT ou d'un TYPE contenu dans un schéma appartenant à un autre utilisateur de base de données. Il s'agit là d'une différence par rapport aux versions antérieures de SQL Server. Pour plus d’informations, consultez OBJECTPROPERTY (Transact-SQL) et TYPEPROPERTY (Transact-SQL).

La propriété des entités suivantes, contenues dans un schéma et de type « objet » peut être transférée : tables, vues, fonctions, procédures, files d'attente et synonymes.

La propriété des entités suivantes ne peut pas être transférée : serveurs liés, statistiques, contraintes, règles, valeurs par défaut, déclencheurs, files d'attente Service Broker, informations d'identification, fonctions de partition, schémas de partition, clés principales de base de données, clé principale de service et notifications d'événements.

La propriété des membres des classes sécurisables suivantes ne peut pas être transférée : serveur, connexion, utilisateur, rôle d'application et colonne.

L'option SCHEMA OWNER n'est valide que lorsque vous transférez la propriété d'une entité contenue dans un schéma. SCHEMA OWNER transférera la propriété de l'entité au propriétaire du schéma dans lequel elle réside. Seules les entités des classes OBJECT, TYPE ou XML SCHEMA COLLECTION sont contenues dans des schémas.

Si l'entité cible n'est pas une base de données et que l'entité est transférée à un nouveau propriétaire, toutes les autorisations de la cible seront supprimées.

Attention

Dans SQL Server 2005 (9.x), le comportement des schémas n'est pas le même que dans les versions antérieures de SQL Server. Un code qui suppose que les schémas sont équivalents aux utilisateurs de base de données peut retourner des résultats incorrects. Vous ne devez pas recourir aux anciens affichages catalogue, notamment sysobjects, dans une base de données où une des instructions DDL suivantes a été utilisée : CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. Dans une base de données où une de ces instructions a été utilisée, vous devez recourir aux nouveaux affichages catalogue. Les nouvelles vues de catalogue prennent en compte la séparation des principaux et des schémas introduite dans SQL Server 2005 (9.x). Pour plus d'informations sur les affichages catalogue, consultez Affichages catalogue (Transact-SQL).

En outre, notez les points suivants :

Important

La seule méthode fiable pour rechercher le propriétaire d’un objet est d’interroger la vue de catalogue sys.objects. La seule méthode fiable pour rechercher le propriétaire d'un type est d'utiliser la fonction TYPEPROPERTY.

Cas particuliers et conditions

Le tableau suivant récapitule les cas particuliers, les exceptions et les conditions qui s'appliquent à l'autorisation de modification.

Classe Condition
OBJECT Impossible de modifier la propriété des déclencheurs, des contraintes, des règles, des valeurs par défaut, des statistiques, des objets système, des files d'attente, des vues indexées ou des tables avec des vues indexées.
SCHEMA Lors d'un transfert de propriété, les autorisations sur des objets contenus dans des schémas n'ayant pas de propriétaires explicites seront supprimées. Impossible de modifier le propriétaire de sys, dbo ou information_schema.
TYPE Impossible de modifier le propriétaire d'un TYPE qui appartient à sys ou information_schema.
CONTRACT, MESSAGE TYPE ou SERVICE Impossible de modifier la propriété des entités système.
SYMMETRIC KEY Impossible de modifier la propriété des clés temporaires globales.
CERTIFICATE ou ASYMMETRIC KEY Impossible de transférer la propriété de ces entités vers un rôle ou un groupe.
ENDPOINT Le principal doit être une connexion.

ALTER AUTHORIZATION pour les bases de données

Pour SQL Server

Conditions requises pour le nouveau propriétaire : Le nouveau principal de propriétaire doit être l’un des éléments suivants :

  • Un compte de connexion d’authentification SQL Server.
  • Un compte de connexion d’authentification Windows représentant un utilisateur Windows (et non un groupe).
  • Un utilisateur Windows qui s’authentifie via un compte de connexion d’authentification Windows représentant un groupe Windows.

Configuration requise pour la personne qui exécute l’instruction ALTER AUTHORIZATION : Si vous n’êtes pas membre du rôle serveur fixe sysadmin, vous devez disposer au moins de l’autorisation TAKE OWNERSHIP sur la base de données et vous devez disposer de l’autorisation IMPERSONATE sur le nouveau compte de connexion de propriétaire.

Pour Azure SQL Database

Conditions requises pour le nouveau propriétaire : Le nouveau principal de propriétaire doit être l’un des éléments suivants :

  • Un compte de connexion d’authentification SQL Server.
  • Un utilisateur fédéré (et pas un groupe) présent dans Azure AD.
  • Un utilisateur géré (et pas un groupe) ou une application présents dans Azure AD.

Si le nouveau propriétaire est un utilisateur Azure Active Directory, il ne peut pas exister comme utilisateur dans la base de données dans laquelle le nouveau propriétaire sera le nouveau propriétaire de la base de données (DBO). Un tel utilisateur Azure AD doit tout d’abord être supprimé de la base de données avant d’exécuter l’instruction ALTER AUTHORIZATION qui affecte la propriété de la base de données au nouvel utilisateur. Pour plus d’informations sur la configuration d’utilisateur Azure AD avec SQL Database, consultez Connexion à SQL Database ou Azure Synapse Analytics en utilisant l’authentification Azure AD.

Configuration requise pour la personne qui exécute l’instruction ALTER AUTHORIZATION : Vous devez vous connecter à la base de données cible pour changer le propriétaire de cette base de données.

Les types de comptes suivants peuvent changer le propriétaire d’une base de données.

  • Le compte de connexion au principal au niveau du service. (L’administrateur SQL Azure provisionné au moment de la création du serveur SQL Database.)
  • L’administrateur Azure Active Directory pour Azure SQL Server.
  • Le propriétaire actuel de la base de données.

Le tableau suivant récapitule les conditions requises :

Exécuteur Cible Résultats
Connexion d’authentification SQL Server Connexion d’authentification SQL Server Succès
Connexion d’authentification SQL Server Utilisateur Azure AD Échec
Utilisateur Azure AD Connexion d’authentification SQL Server Succès
Utilisateur Azure AD Utilisateur Azure AD Succès

Pour vérifier un propriétaire Azure AD de la base de données, exécutez la commande Transact-SQL suivante dans une base de données utilisateur (testdb dans cet exemple).

SELECT CAST(owner_sid as uniqueidentifier) AS Owner_SID
FROM sys.databases
WHERE name = 'testdb';

La sortie est un identificateur (par exemple 6D8B81F6-7C79-444C-8858-4AF896C03C67) qui correspond à l’ObjectID Azure AD assigné à richel@cqclinic.onmicrosoft.com. Quand un utilisateur de connexion d’authentification SQL Server est le propriétaire de la base de données, exécutez l’instruction suivante dans la base de données MASTER pour vérifier le propriétaire de la base de données :

SELECT d.name, d.owner_sid, sl.name
FROM sys.databases AS d
JOIN sys.sql_logins AS sl
ON d.owner_sid = sl.sid;

Bonne pratique

Au lieu d’utiliser des utilisateurs Azure AD comme propriétaires individuels de la base de données, utilisez un groupe Azure AD comme membre du rôle de base de données fixe db_owner. Les étapes suivantes montrent comment configurer un compte de connexion désactivé comme propriétaire de la base de données et rendre un groupe Azure Active Directory (mydbogroup) membre du rôle db_owner.

  1. Connectez-vous à SQL Server comme administrateur Azure AD, puis remplacez le propriétaire de la base de données par un compte de connexion d’authentification SQL Server désactivé. Par exemple, à partir de la base de données utilisateur, exécutez :

    ALTER AUTHORIZATION ON database::testdb TO DisabledLogin;
    
  2. Créez un groupe Azure AD qui doit être propriétaire de la base de données, puis ajoutez-le comme utilisateur à la base de données utilisateur. Par exemple :

    CREATE USER [mydbogroup] FROM EXTERNAL PROVIDER;
    
  3. Dans la base de données utilisateur, ajoutez l’utilisateur qui représente le groupe Azure AD au rôle de base de données fixe db_owner. Par exemple :

    ALTER ROLE db_owner ADD MEMBER mydbogroup;
    

Les membres de mydbogroup peuvent maintenant gérer de manière centralisée la base de données comme des membres du rôle db_owner.

  • Quand des membres de ce groupe sont supprimés du groupe Azure AD, ils perdent automatiquement les autorisations dbo pour cette base de données.
  • Même si de nouveaux membres sont ajoutés au groupe Azure AD mydbogroup, ils obtiennent automatiquement un accès dbo pour cette base de données.

Pour vérifier si un utilisateur spécifique dispose de l’autorisation dbo effective, demandez-lui d’exécuter l’instruction suivante :

SELECT IS_MEMBER ('db_owner');

La valeur de retour 1 indique que l’utilisateur est membre du rôle.

Autorisations

Il faut obligatoirement l'autorisation TAKE OWNERSHIP sur l'entité. Si le nouveau propriétaire n'est pas l'utilisateur qui exécute cette instruction, il faut aussi soit 1) l'autorisation IMPERSONATE sur le nouveau propriétaire s'il s'agit d'un utilisateur ou d'une connexion, soit 2) si le nouveau propriétaire est un rôle, l'appartenance à ce rôle ou l'autorisation ALTER sur le rôle, soit 3) si le nouveau propriétaire est un rôle d'application, l'autorisation ALTER sur le rôle d'application.

Exemples

R. Transfert de la propriété d'une table

L'exemple suivant transfère la propriété de la table Sprockets à l'utilisateur MichikoOsada. La table se trouve dans le schéma Parts.

ALTER AUTHORIZATION ON OBJECT::Parts.Sprockets TO MichikoOsada;
GO

La requête peut aussi se présenter de la façon suivante :

ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada;
GO

Si le schéma d’objets n’est pas inclus en même temps que l’instruction, le Moteur de base de données recherche l’objet dans le schéma par défaut des utilisateurs. Par exemple :

ALTER AUTHORIZATION ON Sprockets TO MichikoOsada;
ALTER AUTHORIZATION ON OBJECT::Sprockets TO MichikoOsada;

B. Transfert de la propriété d'une vue au propriétaire du schéma

L'exemple suivant transfère la propriété de la vue ProductionView06 au propriétaire du schéma qui la contient. La vue se trouve dans le schéma Production.

ALTER AUTHORIZATION ON OBJECT::Production.ProductionView06 TO SCHEMA OWNER;
GO

C. Transfert de la propriété d'un schéma à un utilisateur

L'exemple suivant transfère la propriété du schéma SeattleProduction11 à l'utilisateur SandraAlayo.

ALTER AUTHORIZATION ON SCHEMA::SeattleProduction11 TO SandraAlayo;
GO

D. Transfert de la propriété d'un point de terminaison à un compte de connexion SQL Server

L'exemple suivant transfère la propriété du point de terminaison CantabSalesServer1 à JaePak. Comme le point de terminaison est un élément sécurisable au niveau serveur, il ne peut être transféré qu'à un principal au niveau du serveur.

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.

ALTER AUTHORIZATION ON ENDPOINT::CantabSalesServer1 TO JaePak;
GO

E. Changement du propriétaire d’une table

Chacun des exemples suivants remplace le propriétaire de la table Sprockets dans la base de données Parts par l’utilisateur de base de données MichikoOsada.

ALTER AUTHORIZATION ON Sprockets TO MichikoOsada;
ALTER AUTHORIZATION ON dbo.Sprockets TO MichikoOsada;
ALTER AUTHORIZATION ON OBJECT::Sprockets TO MichikoOsada;
ALTER AUTHORIZATION ON OBJECT::dbo.Sprockets TO MichikoOsada;

F. Changement du propriétaire d’une base de données

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Analytics Platform System (PDW), Azure SQL Database.

L’exemple suivant remplace le propriétaire de la base de données Parts par le compte de connexion MichikoOsada.

ALTER AUTHORIZATION ON DATABASE::Parts TO MichikoOsada;

G. Remplacement du propriétaire d’une base de données SQL par un utilisateur Azure AD

Dans l’exemple suivant, un administrateur Azure Active Directory pour SQL Server d’une organisation avec un annuaire actif nommé cqclinic.onmicrosoft.com peut changer la propriété actuelle d’une base de données targetDB et nommer un utilisateur AAD richel@cqclinic.onmicorsoft.com nouveau propriétaire de la base de données à l’aide de la commande suivante :

ALTER AUTHORIZATION ON database::targetDB TO [rachel@cqclinic.onmicrosoft.com];

Azure AD requiert des crochets [] autour du nom d’utilisateur.

Voir aussi

OBJECTPROPERTY (Transact-SQL)TYPEPROPERTY (Transact-SQL)EVENTDATA (Transact-SQL)