Home
%3CLINGO-SUB%20id%3D%22lingo-sub-989106%22%20slang%3D%22en-US%22%3EAzure%20Portal%20-%20Provide%20permissions%20to%20only%20one%20Azure%20SQL%20Database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-989106%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EScenario%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EYou%20have%20a%20set%20of%20user%60s%20that%20need%20to%20access%20just%20one%20Azure%20SQL%20database%20on%20the%20Azure%20Portal.%20This%20users%20should%20not%20be%20able%20to%20access%20other%20databases%20inside%20the%20same%20logical%20server.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EProblem%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThe%20Azure%20portal%20doesn%60t%20provide%20a%20graphical%20interface%20on%20the%20database%20for%20you%20to%20set%20permissions%20only%20at%20database%20level.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESolution%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWe%20can%20achieve%20our%20final%20goal%20using%20powershell.%3C%2FP%3E%0A%3CP%3EOn%20this%20example%20I%60m%20providing%20only%20Reader%20permissions.%3C%2FP%3E%0A%3CP%3EYou%20can%20find%20details%20on%20RBAC%20roles%20on%20the%20link%20below%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Frole-based-access-control%2Fbuilt-in-roles%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Frole-based-access-control%2Fbuilt-in-roles%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3ENew-AzRoleAssignment%20-RoleDefinitionName%20%22Reader%22%20-SignInName%20my_user%40microsoft.com%20-Scope%20%22%2Fsubscriptions%2FXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX%2FresourceGroups%2FmyResourceGroup%2Fproviders%2FMicrosoft.Sql%2Fservers%2FmyServer%2Fdatabases%2FmyDatabase%22%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20check%20the%20permissions%20on%20the%20resource%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3EGet-AzRoleAssignment%20-Scope%20%22%2Fsubscriptions%2FXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX%2FresourceGroups%2FmyResourceGroup%2Fproviders%2FMicrosoft.Sql%2Fservers%2FmyServer%2Fdatabases%2FmyDatabase%22%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20end%2C%20the%20user%20can%20see%20the%20database%2C%20including%20Metrics%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155371i8F830025464EC6C2%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.jpeg%22%20title%3D%22clipboard_image_0.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20he%20cannot%20see%20the%20server%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155372iE154B029FF7C047B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.jpeg%22%20title%3D%22clipboard_image_1.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20revoke%20the%20access%2C%20simply%20execute%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3ERemove-AzRoleAssignment%20-RoleDefinitionName%20%22Reader%22%20-SignInName%20my_user%40microsoft.com%20-Scope%20%22%2Fsubscriptions%2FXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX%2FresourceGroups%2FmyResourceGroup%2Fproviders%2FMicrosoft.Sql%2Fservers%2FmyServer%2Fdatabases%2FmyDatabase%22%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-989106%22%20slang%3D%22en-US%22%3E%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3E%3CSTRONG%20style%3D%22box-sizing%3A%20border-box%3B%20font-weight%3A%20bold%3B%22%3EScenario%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3EYou%20have%20a%20set%20of%20user%60s%20that%20need%20to%20access%20just%20one%20Azure%20SQL%20database%20on%20the%20Azure%20Portal.%20This%20users%20should%20not%20be%20able%20to%20access%20other%20databases%20inside%20the%20same%20logical%20server.%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3E%3CSTRONG%20style%3D%22box-sizing%3A%20border-box%3B%20font-weight%3A%20bold%3B%22%3EProblem%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3EThe%20Azure%20portal%20doesn%60t%20provide%20a%20graphical%20interface%20on%20the%20database%20for%20you%20to%20set%20permissions%20only%20at%20database%20level.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-989106%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20Database%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E

 

 

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"