Share via

How to give an AAD group view only access to a single database within SQL Server with multiple databases

Abdul Asim 0 Reputation points
2024-06-19T01:44:05.8133333+00:00

I have a SQL Server in my subscription with multiple databases. Each database corresponds to a client organization. I wanted to give each client read only access to their individual database without them being able to see the other databases.

For a single client,
I created a Group using Entra ID and added the external users to this group.
Set the SQL Server Microsoft Entra Admin
Then I logged into the client's database
Then I Used the Azure portal's Query Editor to manage client's database permissions.

I ran the following commands:
CREATE USER [ClientDBViewOnlyAccess] FOR EXTERNAL PROVIDER GO
ALTER ROLE db_datareader ADD MEMBER [ClientDBViewOnlyAccess] GO

where ClientDBViewOnlyAccess is the AAD group name
This successfully gave the group reader access at the database level.

However, after a user from the group logs into Azure, they do not see anything and get a message "You do not have any Azure subscriptions in the directory".
I figured this was because the group does not have permission at the resource / subscription level.
First I assigned reader role to the group at subscription level, but then they were able to see all resources including different ADFs etc which was unacceptable. I removed the role from the subscription level and assigned the reader role at the SQL server level to the group.

Now, they were able to login and see only the SQL server but they were also able to see the list of all the other databases other than their own. They were only able to login to their database, not the others.

What do i need to do to make sure that the group logs into Azure, goes to the SQL server but can only see their database and not the others.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,166 questions
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
20,275 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee
    2024-06-19T08:06:56.5333333+00:00

    Hi Abdul Asim •,

    Welcome to Microsoft Q&A forum.

    As I understand, you want to give an AAD group view only access to a single database within SQL Server with multiple databases.

    In a way, you want to hide all other Azure SQL Databases from a particular user in a single SQL Server.

    Please refer to a similar post here https://stackoverflow.com/questions/75585767/how-to-hide-databases-in-azure-sql

    if this is related to your ask.

    If not, please state with more details so that we can understand and assist you.

    Thanks

    0 comments