First published on MSDN on Oct 23, 2018
Sometimes a user is assigned permissions at Resource Group level or Resource level.
When using Long-Term Retention we have several advantages like each backup will be kept in the long-term storage for the period specified by these parameters or the database can be restored to any existing server under the same subscription as the original database.
Due to this, the backups are associated with the Subscription and not the Resource Group or Server.
A common issue is that the users with permissions at Resource Group level or Resource level will not be able to see the backups.
The user need some read permissions at Subscription level to be able to see the backups.
You can see more details about the roles at
Unable to view LTR Backups for Azure SQL Database servers
When it's not possible to assign permissions at Subscription level or we wish to keep the users with as least privileges as possible, the subscription administrator can create a custom role with just that read permissions and assign it to the users/groups that should see the LTR backups.
Please replace {SubscriptionId} and {user@company.com} accordingly.
[code language="powershell"]
#Login to Azure
Login-AzureRmAccount
#List the subscriptions you have access
Get-AzureRmSubscription
#Select the subscription you want to use in case you have access to more than one subscription
Select-AzureRmSubscription -Subscription "{SubscriptionId}"
#Create the custom role
$role = Get-AzureRmRoleDefinition "Reader"
$role.Id = $null
$role.Name = "Long Term Retention Backups - Read"
$role.Description = "Can view the Long Term Retention Backups."
$role.Actions.Clear()
$role.Actions.Add("Microsoft.Sql/locations/longTermRetentionBackups/read")
$role.Actions.Add("Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionBackups/read")
$role.Actions.Add("Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionDatabases/longTermRetentionBackups/read")
$role.AssignableScopes.Clear()
$role.AssignableScopes.Add("/subscriptions/<SubscriptionId>")
New-AzureRmRoleDefinition -Role $role
#List all the roles, confirm that the custome role is available, it can take some time
#Get-AzureRmRoleDefinition | FT Name, IsCustom
#Assign the role
New-AzureRmRoleAssignment -RoleDefinitionName "Long Term Retention Backups - Read" -SignInName "{user@company.com}" -Scope "/subscriptions/{SubscriptionId}"
#Wait some seconds and the user should be able to see the backups now
[/code]
(2018-10-24 update)
In case you get the following error: "
New-AzureRmRoleAssignment : The provided information does not map to an AD object id.
" this means that the SignInName is not exactly like this, specially with guest users.
If you run the following you will be able to understand the ObjectID you should use:
[code language="powershell"]
Connect-AzureAD
Get-AzureADUser -SearchString "Vitor Tomaz"
[/code]
You should get a result like the following, if not, it means you are probably not using the right 'Name':
[code language="powershell"]
ObjectId DisplayName UserPrincipalName UserType
-------- ----------- ----------------- --------
4db73255-9e71-4e35-9157-ea53d62d63a0 Vitor Tomaz vitor.tomaz_something.com#EXT#@somethingelse.onmicrosoft.com Guest
[/code]
You can now use the ObjectId from the previous result to assign the role:
[code language="powershell"]
#Assign the role using ObjectId
New-AzureRmRoleAssignment -RoleDefinitionName "Long Term Retention Backups - Read" -ObjectId 4db73255-9e71-4e35-9157-ea53d62d63a0 -Scope "/subscriptions/{SubscriptionId}"
[/code]