CODEWITHSUNDEEP
azure-sql-databaseazure-active-directory
Kristen W
Kristen W

Reputation: 3

Azure SQL Database User Not Inheriting Role Permissions

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.

Upvotes: 0

Views: 790

Answers (1)

axfd
axfd

Reputation: 321

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.

enter image description here enter image description here

But I run the command:

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

It shows that the user has select permission.

enter image description here

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

Upvotes: 2

Related Questions