Use Azure Powershell Runbook to add AzureAD user as db_owner to an Azure SQL database

Turns out that there is an undocumented way to do this. Discovered it with the help of Azure support team.

The SQL query to be used is actually:

CREATE USER [[email protected]] WITH SID = $hexSid, Type E;

Here $hexSid can be obtained by the following SQL query

1. Get the ObjectId of the AzureAD User

$objectId = (Get-AzureADUser -ObjectId "[email protected]").ObjectId

2. Use the SQL below to get the sid

DECLARE @sid = uniqueidentifier = cast('$objectId' as uniqueidentifier) select cast(@sid as varbinary(max))

3. Now ideally, the effective query would have directly used the @sid but that is not possible since the With SID needs a hard coded value. So I had to process the output of the sid query separately like below:

$result = $command.ExecuteScalar() # result is byte array
$hexSid = ($result | ForEach-Object ToString X2) -Join ''  # convert to hex format
$hexSid = "0x$hexSid"

With those changes the code in the question just works fine!

Effectively, we are only passing the AzureAD User's objectId in the Create User query.

Complete code:

$targetUser = "[email protected]"
$sqlServerName = "someserver"
$dbName = "somedb"
$accessToken = "" # obtained from SPN connection  

# Get SQL SID for the targeted user
Write-Output "Getting AzureADUser $targetUser "
$objectId = (Get-AzureADUser -ObjectId $targetUser).ObjectId
$sidQuery = "DECLARE @sid uniqueidentifier = cast('$objectId' as uniqueidentifier) select cast(@sid as varbinary(max))"
$sidOutput = New-Object -TypeName HashTable
Write-Output "Invoking SQL Query execution to get SID for objectId: $objectId ($targetUser)"
Invoke-OrExecuteSqlQuery -ServerName $sqlServerName -DbName $dbName -Query $sidQuery `
    -AccessToken $accessToken -Output $sidOutput
$sidBytes = $sidOutput.Result
Write-Output "SID bytes: $sidBytes" 
$hexSid = ($sidBytes | ForEach-Object ToString X2) -Join ''  # convert to hex format
$hexSid = "0x$hexSid"
Write-Output "SQL SID is : $hexSid"

# Add AzureAD user to the database
Write-Output "Adding AzureAD user : $targetUser to the db/dw: $dbName"
$createDBUserQuery = "CREATE USER [$targetUser] WITH SID = $hexSid, Type = E;" # create db user
$createUserOutput = New-Object -TypeName HashTable
Invoke-OrExecuteSqlQuery -ServerName $sqlServerName -DbName $dbName -Query $createDBUserQuery `
    -AccessToken $accessToken -Output $createUserOutput
Write-Output "Created DB User : $targetUser on the DB : $dbName"

# Set targeted user as the database owner
Write-Output "Setting database admin for db/dw: $dbName"
$makeDBOwnerQuery = "ALTER ROLE db_owner ADD MEMBER [$targetUser];"
$alterSQLDBRoleOutput = New-Object -TypeName HashTable
Invoke-OrExecuteSqlQuery -ServerName $sqlServerName -DbName $dbName -Query $makeDBOwnerQuery `
    -AccessToken $accessToken -Output $alterSQLDBRoleOutput
Write-Output "Made $targetUser as the DB Owner"

function Invoke-OrExecuteSqlQuery {
    param (
        [string] $ServerName,
        [string] $DbName,
        [string] $Query,
        [string] $AccessToken,
        [hashtable] $Output
    )
    $server = "$ServerName.database.windows.net"
    $database = $DbName
    $query = $Query

    Write-Output "Connecting to the SQL DB: $database on the SQL Server: $server"
    $connectionString = "Data Source=$server;Initial Catalog=$database;Connect Timeout=30;"
    $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
    $connection.AccessToken = $AccessToken

    Write-Output "Executing SQL query.."
    $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
    $connection.Open()
    $result = $command.ExecuteScalar()
    $connection.Close()
    Write-Output "Execution of SQL query complete"

    if ($result) {
        $Output.Result = $result
    }
}