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
sp_addrolememberandsp_droprolememberstill work;ALTER ROLE ... ADD MEMBERis the current syntax.- Contained database users (
FROM EXTERNAL PROVIDER, or with a password) are created per database, whereasCREATE LOGINis run inmaster.