LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon