Azure SQL Database User Not Inheriting Role Permissions

Question

I have created contained database roles in an Azure SQL database, granted permissions to the roles, and created database users mapped to Azure Active Directory, and added the database users to the applicable roles. However, the users don't appear to be automatically inheriting the permissions of the roles (I've confirmed they were successfully added). Am I missing a step?

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER ALTER ROLE [DATABASE-ROLE] ADD MEMBER [[email protected]]

Note: The roles have Select and View Definition permissions, but the users are only showing Connect permissions.

axfd axfd · Accepted Answer · 2018-08-02T05:08:04

I have the same issue. I run ran the command as below to create a role which has select permission and assign the role to a Azure AD user.

CREATE ROLE testrole AUTHORIZATION [dbo]
GO

-- Grant access rights to a specific schema in the database
GRANT 
    SELECT, 
    VIEW DEFINITION 
ON SCHEMA:: [dbo]
    TO testrole
GO
create user [[email protected]] from EXTERNAL PROVIDER
GO
ALTER ROLE testrole ADD MEMBER [[email protected]]
GO

Then I run the command :

    SELECT p.NAME
    ,m.NAME
    FROM sys.database_role_members rm
    JOIN sys.database_principals p
    ON rm.role_principal_id = p.principal_id
    JOIN sys.database_principals m
    ON rm.member_principal_id = m.principal_id 
    GO
    SELECT DISTINCT pr.principal_id, pr.name, pr.type_desc, 
    pr.authentication_type_desc, pe.state_desc, pe.permission_name
    FROM sys.database_principals AS pr
    JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id;

It shows that I have assigned the role to the user ans the use only has connect permission.

But I run the command:

EXECUTE AS user = '[email protected]'  
select * from student

It shows that the user has select permission.

So I think Transact-SQL support Azure AD user in Azure SQL database is not perfect. Is that right? Can someone explain it?

Azure SQL Database User Not Inheriting Role Permissions

1 Answers