Azure Portal - Provide permissions to only one Azure SQL Database
Published Nov 06 2019 09:44 AM 3,674 Views

 

 

Scenario:

You have a set of user`s that need to access just one Azure SQL database on the Azure Portal. This users should not be able to access other databases inside the same logical server.

 

Problem:

The Azure portal doesn`t provide a graphical interface on the database for you to set permissions only at database level.

 

Solution:

We can achieve our final goal using powershell.

On this example I`m providing only Reader permissions.

You can find details on RBAC roles on the link below:

https://docs.microsoft.com/en-us/azure/role-based-access-control/built-in-roles

 

New-AzRoleAssignment -RoleDefinitionName "Reader" -SignInName my_user@microsoft.com -Scope "/subscriptions/XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX/resourceGroups/myResourceGroup/providers/Microsoft.Sql/servers/myServer/databases/myDatabase"

 

To check the permissions on the resource:

 

Get-AzRoleAssignment -Scope "/subscriptions/XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX/resourceGroups/myResourceGroup/providers/Microsoft.Sql/servers/myServer/databases/myDatabase"

 

 

In the end, the user can see the database, including Metrics

clipboard_image_0.jpeg

 

But he cannot see the server:

 

clipboard_image_1.jpeg

 

To revoke the access, simply execute:

 

Remove-AzRoleAssignment -RoleDefinitionName "Reader" -SignInName my_user@microsoft.com -Scope "/subscriptions/XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX/resourceGroups/myResourceGroup/providers/Microsoft.Sql/servers/myServer/databases/myDatabase"

 

Version history
Last update:
‎Nov 06 2019 09:44 AM
Updated by: