Grant access to multiple databases via Azure AD in sql database

1
votes

Description

We use Azure SQL database with multiple databases on a server. It is possible to grant permissions to a single database via the user's Azure AD login by creating a group, say "DBReader". in AAD and assign the group to the role "Reader" via the server's settings in azure portal and then create a user when connected to the database as CREATE USER [DBReaders] FROM EXTERNAL PROVIDER, which will allow connecting to the single database.

Problem

We'd like to grant read access to all databases, so that the user sees all databases with a single connection and must not add them separately. Normally, you'd create a login on the server for this. However, the preview feature https://docs.microsoft.com/en-gb/sql/t-sql/statements/create-login-transact-sql?view=azuresqldb-mi-current, which would allow CREATE LOGIN ... FROM EXTERNAL PROVIDER is not available for Azure SQL database.

Question

Is there any way we did not think of to simply grant access to all databases via an AAD group?

1
sql-serverazure-active-directoryazure-sql-database
If you want this functionality (and other on-premises functionality), Managed Instance may be a better fit. But what is the actual issue with granting the same AAD group access to multiple databases? – Nick.McDermaid
Hi @Nick.McDermaid, the problem is that we then must add connections for each database separately (e.g. management studio, linqpad), as one must specify the DB on connect if it is a per-database user. A suitable solution would involve the user to being able to see all databases he has permissions to at once. – SVSchmidt

1 Answers

1
votes

Is there any way we did not think of to simply grant access to all databases via an AAD group?

No. Outside of Managed Instance, which requires a minimum of 4 vCores, Azure SQL Database users must be added to each database.

A suitable solution would involve the user to being able to see all databases he has permissions to at once

For Azure SQL Database, this requires the client to connect to Master to, and then reconenct to switch databases. SQL Server Management Studio does this, but other clients may not.