LLI Wiki — My Digital Knowledge Base

Azure SQL Database: logins, Entra ID users and roles

Create logins, Microsoft Entra ID users and roles in Azure SQL Database, then audit permissions.

This article gathers the T-SQL statements used to create and manage logins, users and roles in Azure SQL Database, including Microsoft Entra ID (Azure AD) users, and to audit the resulting permissions.

Create a Microsoft Entra ID user

Connected to the user database as an Entra ID administrator, create a contained user from the external provider:

CREATE USER [user@example.com] FROM EXTERNAL PROVIDER;

-- optional default schema
CREATE USER [user@example.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo];

-- remove it again
DROP USER [user@example.com];

An Entra ID group can be used the same way, by replacing the user name with the group name.

Create a SQL login and user

Run in the master database of the logical server:

CREATE LOGIN app_login WITH PASSWORD = '<PASSWORD>';
GO

To allow a login to create databases and other logins, create a user in master and add it to the dbmanager and loginmanager roles:

CREATE USER admin_user FROM LOGIN admin_user;
GO
EXEC sp_addrolemember 'loginmanager', 'admin_user';
GO
EXEC sp_addrolemember 'dbmanager', 'admin_user';
GO

Members of dbmanager and loginmanager can view the sys.sql_logins system view.

Then, in a new query window connected to the target database, create the user and grant a role:

CREATE USER [app_user] FOR LOGIN [app_login] WITH DEFAULT_SCHEMA=[dbo];
GO
EXEC sp_addrolemember 'db_owner', 'app_user';
GO

A login can also be created disabled, for example for a backup product, and enabled later:

CREATE LOGIN [svc_backup] WITH PASSWORD='<PASSWORD>';
GO
ALTER LOGIN [svc_backup] DISABLE;
GO

Never store real passwords in scripts or notes.

Add and remove role members

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';

-- custom database role
EXEC sp_addrolemember N'app_sql_user', N'app_user';
EXEC sp_droprolemember N'app_sql_user', N'app_user';

Check roles and permissions

Role membership in the database

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;

Explicit permissions in the database

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;

Entra ID (external) principals

SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE authentication_type = 1;  -- External = Azure AD

Roles of a given user or group

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';

List database roles

SELECT name, type_desc
FROM sys.database_principals
WHERE type_desc = 'DATABASE_ROLE';

Permissions granted to roles

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;

Restrict metadata visibility

To stop a role from seeing object definitions:

REVOKE VIEW DEFINITION FROM [app_sql_reader];

Notes

Mastodon LLIMastodon SocialFosstodon