Azure SQL Database : logins, utilisateurs Entra ID et rôles
Créer des logins, utilisateurs Microsoft Entra ID et rôles dans Azure SQL Database, puis auditer les permissions.
Cet article rassemble les instructions T-SQL pour créer et gérer des logins, utilisateurs et rôles dans Azure SQL Database, y compris les utilisateurs Microsoft Entra ID (Azure AD), et pour auditer les permissions obtenues.
Créer un utilisateur Microsoft Entra ID
Connecté à la base utilisateur en tant qu'administrateur Entra ID, créer un utilisateur contenu à partir du fournisseur externe :
CREATE USER [user@example.com] FROM EXTERNAL PROVIDER;
-- schéma par défaut optionnel
CREATE USER [user@example.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo];
-- le supprimer
DROP USER [user@example.com];
Un groupe Entra ID s'utilise de la même façon, en remplaçant le nom d'utilisateur par le nom du groupe.
Créer un login et un utilisateur SQL
À exécuter dans la base master du serveur logique :
CREATE LOGIN app_login WITH PASSWORD = '<PASSWORD>';
GO
Pour autoriser un login à créer des bases et d'autres logins, créer un utilisateur dans master et l'ajouter aux rôles dbmanager et loginmanager :
CREATE USER admin_user FROM LOGIN admin_user;
GO
EXEC sp_addrolemember 'loginmanager', 'admin_user';
GO
EXEC sp_addrolemember 'dbmanager', 'admin_user';
GO
Les membres de dbmanager et loginmanager peuvent consulter la vue système sys.sql_logins.
Ensuite, dans une nouvelle fenêtre de requête connectée à la base cible, créer l'utilisateur et lui attribuer un rôle :
CREATE USER [app_user] FOR LOGIN [app_login] WITH DEFAULT_SCHEMA=[dbo];
GO
EXEC sp_addrolemember 'db_owner', 'app_user';
GO
Un login peut aussi être créé désactivé, par exemple pour un produit de sauvegarde, puis activé plus tard :
CREATE LOGIN [svc_backup] WITH PASSWORD='<PASSWORD>';
GO
ALTER LOGIN [svc_backup] DISABLE;
GO
Ne jamais conserver de vrais mots de passe dans des scripts ou des notes.
Ajouter et retirer des membres de rôle
EXEC sp_addrolemember 'db_owner', 'app_user';
EXEC sp_droprolemember 'db_owner', 'app_user';
EXEC sp_addrolemember 'db_datareader', 'app_user';
EXEC sp_addrolemember 'db_datawriter', 'app_user';
-- rôle de base de données personnalisé
EXEC sp_addrolemember N'app_sql_user', N'app_user';
EXEC sp_droprolemember N'app_sql_user', N'app_user';
Vérifier les rôles et permissions
Appartenance aux rôles dans la base
SELECT dp.name AS database_principal,
dp.type_desc,
rp.role_principal_id,
rp.member_principal_id,
pr.name AS role_name
FROM sys.database_role_members rp
JOIN sys.database_principals dp ON rp.member_principal_id = dp.principal_id
JOIN sys.database_principals pr ON rp.role_principal_id = pr.principal_id
ORDER BY dp.name;
Permissions explicites dans la base
SELECT
grantee_principal.name AS grantee,
permission_name,
state_desc,
class_desc,
p.major_id,
o.name AS object_name
FROM sys.database_permissions p
JOIN sys.database_principals grantee_principal
ON p.grantee_principal_id = grantee_principal.principal_id
LEFT JOIN sys.objects o
ON p.major_id = o.object_id AND p.class_desc = 'OBJECT_OR_COLUMN'
ORDER BY grantee;
Principals Entra ID (externes)
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE authentication_type = 1; -- External = Azure AD
Rôles d'un utilisateur ou groupe donné
SELECT DP1.name AS DatabaseRoleName,
DP2.name AS DatabaseUserName
FROM sys.database_role_members AS DRM
INNER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
INNER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP2.name = 'app_sql_group';
Lister les rôles de base de données
SELECT name, type_desc
FROM sys.database_principals
WHERE type_desc = 'DATABASE_ROLE';
Permissions accordées aux rôles
SELECT
dp.name AS role_name,
p.permission_name,
p.state_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name
FROM sys.database_permissions p
JOIN sys.database_principals dp
ON p.grantee_principal_id = dp.principal_id
WHERE dp.type_desc = 'DATABASE_ROLE'
ORDER BY role_name, object_name;
Restreindre la visibilité des métadonnées
Pour empêcher un rôle de voir les définitions des objets :
REVOKE VIEW DEFINITION FROM [app_sql_reader];
Remarques
sp_addrolememberetsp_droprolememberfonctionnent toujours ;ALTER ROLE ... ADD MEMBERest la syntaxe actuelle.- Les utilisateurs de base contenus (
FROM EXTERNAL PROVIDER, ou avec mot de passe) sont créés par base, alors queCREATE LOGINs'exécute dansmaster.