Set specific database role permissions Azure SQL
Introduction
I had to set permissions on multiple databases on an Azure SQL server. I decided to create a function for this that can set specific database role permissions on all or one SQL database within an Azure SQL server.
The script uses sqlcmd to connect to the Azure SQL server and configure the permissions. So apart from PowerShell, you will need to have the Az.Accounts PowerShell module and the SqlServer PowerShell module installed. Or you can automate it even further by using an Azure DevOps pipeline or GitHub action that first installs PowerShell and the required modules.
Of course you need to either connect using the Sql administrator account or the Microsoft Entra admin for Azure Sql. (The Entra admin can also be a security group in Entra Id)
SqlCmd is very picky about quotes, especially if your Entra ID group has spaces, so the script does some replacing of single quotes (‘) with two single quotes (‘’).
The script
Without further ado, here’s the script:
| #Requires -Module Az.Accounts,SqlServer | |
| function Set-AzSqlDbUserPermissions { | |
| <# | |
| .SYNOPSIS | |
| Function to set SQL permissions on an Azure SQL server | |
| .DESCRIPTION | |
| A PowerShell function to set specific database role permissions | |
| on all or one SQL database within an Azure SQL server | |
| .PARAMETER ResourceGroupName | |
| The name of the Azure Resource Group that holds the SQL server | |
| .PARAMETER SqlServerName | |
| The full SQL server name, including ".database.windows.net" | |
| .PARAMETER SqlDatabaseName | |
| The name of the SQL database. If not specified, all user databases will be processed | |
| .PARAMETER EntraIdGroupName | |
| The name of the EntraID group to assign the SQL permissions to. | |
| .PARAMETER DatabaseRole | |
| The database role to assign to the group. | |
| .NOTES | |
| sqlDbToolbox.ps1 | |
| By Marco Janse | |
| Versioning: | |
| - Version Number: 1.0 | |
| - Last Modified date: 05/04/2024 | |
| Version history: | |
| Version Changed by Changes | |
| --------------------------------------------------------------------------------------------------------------------- | |
| 1.0 Marco Janse Published function as a gist | |
| 0.2 Marco Janse add try/catch block, EntraID rename for param, | |
| define required modules, Parameter help, sql query fix | |
| 0.1 Marco Janse Initial function to test | |
| .LINK | |
| https://gist.github.com/MarcoJanse/f78534f8d22a9b215a476a122ac5e6aa | |
| .EXAMPLE | |
| Set-AzSqlDbUserPermissions -sqlServerName "YourSqlServerName" -EntraIdGroupName "AD Group Name" -Role "db_datareader" | |
| #> | |
| [CmdletBinding()] | |
| param( | |
| [Parameter(Mandatory = $true)] | |
| [string]$ResourceGroupName, | |
| [Parameter(Mandatory = $true)] | |
| [string]$SqlServerName, | |
| [Parameter(Mandatory = $false)] # If not specified, it will apply to all databases | |
| [string]$SqlDatabaseName = $null, | |
| [Parameter(Mandatory = $true)] | |
| [string]$EntraIdGroupName, | |
| [Parameter(Mandatory = $true)] | |
| [ValidateSet('db_datareader', 'db_datawriter', 'db_owner', 'db_securityadmin', 'db_accessadmin', 'db_backupoperator', 'db_ddladmin', 'db_denydatareader', 'db_denydatawriter')] # You can modify this list based on your needs | |
| [string]$DatabaseRole | |
| ) | |
| BEGIN { | |
| $Token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).token | |
| # Multi-line query using Access token to map a group to a SQL database with specified role | |
| $escapedGroupName = $EntraIdGroupName.Replace("'", "''") | |
| $sqlCmdQuery = @" | |
| IF NOT EXISTS ( | |
| SELECT 1 FROM sys.database_principals WHERE name = '$escapedGroupName' | |
| ) | |
| BEGIN | |
| CREATE USER [$escapedGroupName] FROM EXTERNAL PROVIDER; | |
| EXEC sp_addrolemember '$DatabaseRole', '$escapedGroupName'; | |
| SELECT 'User created and role assigned' as ActionTaken; | |
| END | |
| ELSE | |
| BEGIN | |
| SELECT 'User already exists' as ActionTaken; | |
| END | |
| "@ | |
| Write-Verbose -Message 'Below the entire SqlCmdQuery' | |
| Write-Verbose $sqlCmdQuery | |
| $Params = @{ | |
| ServerInstance = "$sqlServerName.database.windows.net" | |
| AccessToken = $Token | |
| Query = $sqlCmdQuery | |
| } | |
| Write-Verbose -Message "The following Parameters have been set:" | |
| Write-Verbose $Params | |
| } # BEGIN | |
| PROCESS { | |
| try { | |
| # If $sqlDatabaseName is not specified, loop through all databases on the server | |
| if (-not $sqlDatabaseName) { | |
| $databases = (Get-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $sqlServerName | | |
| Where-Object { | |
| $_.DatabaseName -ne 'master' } | |
| ).DatabaseName | |
| Write-Verbose -Message "Found $($databases.count) databases" | |
| if ($null -ne $databases) { | |
| foreach ($db in $databases) { | |
| Write-Verbose -Message "Processing $db ..." | |
| $params.Database = $db | |
| Invoke-SqlCmd @params | |
| } | |
| } | |
| else { | |
| Write-Error "No databases found for the specified SQL Server." | |
| } | |
| } | |
| else { | |
| $params.Database = $sqlDatabaseName | |
| Invoke-SqlCmd $params | |
| } | |
| } | |
| catch { | |
| Write-Error "Error encountered: $_" | |
| } | |
| } # PROCESS | |
| END { | |
| Write-Verbose "End of function" | |
| } | |
| } # Set-AzSqlDbUserPermissions |
Closing notes
I hope someone will make good use of this script or make it even better.