Azure SQL Database

Fernando Gutierrez 240

Hello

I have a question about something that is happening to me regarding access to SQL Databases

When I give a group or a user, after creating the Login in the SQL Server, access to the databases that it contains within that same SQL Server, if I give them the Reader ROLE, once that or those users log into the database, it forces them to have to punctually place each one of the databases that the SQL Server contains.

This is very tedious since they have to place each one of those databases that the server contains.

On the other hand, when I assign them the Writer ROLE or Contributor ROLE, once they log into a SQL Server, it brings them all the databases it contains.

How can I solve this point, so that users with the Reader ROLE get all the databases on the Server.

Thanks!

Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2024-09-18T21:01:11.85+00:00

@Fernando Gutierrez Thank you for reaching out.

Could you please share a bit more details on what is not working.

My understanding is that you gave a group of users' access read role to a db and then I am not understanding this part "once they log into a SQL Server, it brings them all the databases it contains." and " it forces them to have to punctually place each one of the databases that the SQL Server contains."

Is it for an Azure SQL DB?
Fernando Gutierrez 240 Reputation points

2024-09-24T15:14:04.1933333+00:00

Hello @Oury Ba-MSFT

Correct, it is for a SQL database in Azure.

The SQL Server you are accessing has approximately 12 or 15 databases within it.

Those users who are within the group with the Read role and permission, when they enter that SQL Server, are forced to have to place each database of those 12 or 15 one by one.

While users who have the Writer role, for example, once they enter that SQL Server, it brings them all the databases.
Erland Sommarskog 112.7K Reputation points MVP

2024-09-24T20:51:10.86+00:00

Exactly how did you set up these roles? "Contributor" is not really an SQL Server term, so might you have set this with general RBAC in Azure?
Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2024-10-03T22:54:10.47+00:00
@Fernando Gutierrez

Azure SQL Database uses Role-Based Access Control (RBAC) to manage permissions.

If needed, adjust the role’s permissions to allow listing all databases.

You will need to go through the below docs to understand how Azure user permissions work.

Azure Built-In Roles - Azure RBAC | Microsoft Learn:

This article provides a comprehensive list of Azure built-in roles that you can assign to users, groups, service principals, and managed identities.

It explains each role’s purpose and permissions.

Azure Built-In Roles - Azure RBAC

Tutorial: Grant a User Access to Azure Resources Using the Azure Portal:

This step-by-step tutorial demonstrates how to grant a user access to create and manage virtual machines in a resource group using the Azure portal and Azure RBAC.

Tutorial: Grant a User Access to Azure Resources Using the Azure Portal

Permissions, Security Groups, and Service Accounts Reference:

This reference guide provides detailed information about each built-in user, group, and permission.

It covers various aspects of permissions and access control.

Permissions, Security Groups, and Service Accounts Reference

https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage?view=azuresql

Please let me know by commenting below if you need further assistance on this.
Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2024-10-04T18:32:14.9466667+00:00

@Fernando Gutierrez Checking if the above answer were helpful.

Regards,

Oury

1 answer

Olaf Helper 45,106 Reputation points

2024-09-18T18:14:34.78+00:00

place each one of the databases that the SQL Server contains. with the Reader ROLE get all the databases on the Server.

Seems you haven't understand in any way how "Azure SQL Database" works.

You don't get a server, you get one database.

Every database is a separate unit with separate user and separat permissions, if you have many "Azure SQL Database" then the is no relation between, beside your subscription.

No way to get what you are looking for.
Please sign in to rate this answer.
Fernando Gutierrez 240 Reputation points

2024-09-18T19:07:23.2066667+00:00

Sorry

If I understood how it works, because as I mentioned in the question, for those users who are configured with a writer or contributor role, it DOES bring all the databases from the SQL engine.

The problem is with those users or groups who are configured with a reader ROLE

The truth is that your answer is completely incomprehensible.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure SQL Database

1 answer

Your answer