My requirement I have 20 users all are configured in azure active directory resource group. I have one Azure SQL Server under I have 3 Azure SQL Databases like Dev, QA and UAT In Active Directory configured 20 users I need different access in each database level, some people need only read access and some people need both write and read, etc. Please help me to do this. Is it possible to create users group in each database level with different roles for azure active directory users? users can login SSMS with azure active directory credentials but each user having different access in each database
2 Answers
0
votes
you can create groups in AAD and provide the groups/users access on the database individually.
Execute the below queries in individual databases: 1) CREATE USER [GroupName] FROM EXTERNAL PROVIDER or CREATE USER [[email protected]] FROM EXTERNAL PROVIDER
2) exec sp_addrolemember 'db_datareader', '<>' or exec sp_addrolemember 'dbowner', '<>' etc based on the roles which you want to assign.
Note : Since the user is not created at server level and directly at databases level, while trying to connect via SSMS ,
Put the database name explicitly.
0
votes
Per my experience, you need manually configure the AAD users with different permissions or database role in each database.
In addition, you need connect to the Azure SQL with Azure AD admin account, only the admin account can do the operations for each database.
For example, one of the AAD user '[email protected]' only needs have the read permission in database Dev but needs read/writer permission in database QA and UAT. User '[email protected]' needs read/writer permission in database 'Dev' and read permission in 'QA' and 'UAT'. In each database, different users are derived to two roles. Then we can not configure group level permission for these AAD users in each database.
After doing that, all the users in your AAD group will have the different permissions in different database. When connect to the database with SSMS, current user can see all the databases which he has the permission to access. You also can choose the specific database in SSMS connection properties like @Nandan said.
HTH.
