Azure / SQL Server / Azure SQL Database Disaster Recovery / Orphaned Users / Different SIDs

-- ======================================================================================
-- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database
-- ======================================================================================
CREATE LOGIN <SQL_login_name, sysname, login_name>
WITH PASSWORD = '<password, sysname, Change_Password>'
GO

-- ======================================================================================
-- Create SQL Login template for Azure SQL Database with SID
-- ======================================================================================
CREATE LOGIN <SQL_login_name, sysname, login_name>
WITH PASSWORD = '<password, sysname, Change_Password>',
SID= SID_VALUE
GO

SELECT name, sid, type_desc, principal_id
FROM sys.sql_logins

SELECT name, sid, type_desc, principal_id
FROM sys.database_principals
WHERE type = 'S' AND sid IS NOT NULL AND authentication_type=1

CREATE LOGIN test_user
WITH PASSWORD = 'test_user_password',
SID= 0x01060000000000640000000000000000AFABF8972EFF7042B58BD03765EA804E
GO

Connect-AzureRmAccount
Get-AzureRmSubscription
Select-AzureRmSubscription -SubscriptionId xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx
Get-AzureRmContext

param (
# Set an admin login and password for your server
[string]$adminlogin = "",
[string]$password = "",

# Set server name - the logical server name has to be unique
[string]$servername = "",

# Set failover server name - the logical failover server name has to be unique
[string]$failoverservername = "",

# The database name
[string]$databasename = "",

# Database (App) user
[string]$databaseuser = "",
[string]$databaseuserpassword = "",
[string]$databaseuserrole="db_owner"
)
cls

#########################################################################
# Create a login for the SQL Server and add user to the database (Primary Server)
#########################################################################
# Create a login
$ConnStrMaster = @{
'Database' = 'master'
'ServerInstance' = $servername+'.database.windows.net'
'Username' = $adminlogin
'Password' = $password
'Query' = "IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = N'$databaseuser')
CREATE LOGIN $databaseuser WITH PASSWORD=N'$databaseuserpassword'"
}
Invoke-Sqlcmd @ConnStrMaster

# Create DB user and map created login to DB user
$ConnStrDB = @{
'Database' = $databasename
'ServerInstance' = $servername+'.database.windows.net'
'Username' = $adminlogin
'Password' = $password
'Query' = "IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'$databaseuser')
CREATE USER $databaseuser
FOR LOGIN $databaseuser
GO
EXEC sp_addrolemember N'$databaseuserrole', N'$databaseuser'
GO"
}
Invoke-Sqlcmd @ConnStrDB
#########################################################################
#########################################################################

#########################################################################
# Find SID for created user (match SID)
#########################################################################
$SIDMatch = @{
'Database' = $databasename
'ServerInstance' = $servername+'.database.windows.net'
'Username' = $adminlogin
'Password' = $password
'Query' = "SELECT sid FROM sys.database_principals WHERE name = N'$databaseuser'"
}
$results=Invoke-Sqlcmd @SIDMatch
$sidBytes = $results.sid
$sidText = "0x"+ (($sidBytes|ForEach-Object ToString X2) -join '')
$resultsNew=$sidText.Substring(0, 66)
#########################################################################
#########################################################################
###################################################################
# Create a login for the SQL Server (Failover Server)
#########################################################################
$ConnStrMasterFailover = @{
'Database' = 'master'
'ServerInstance' = $failoverservername+'.database.windows.net'
'Username' = $adminlogin
'Password' = $password
'Query' = "IF (EXISTS(SELECT * FROM sys.sql_logins WHERE [name] = N'$databaseuser')) DROP LOGIN [$databaseuser];
CREATE LOGIN $databaseuser WITH PASSWORD=N'$databaseuserpassword', SID =$resultsNew"
}
Invoke-Sqlcmd @ConnStrMasterFailover
#########################################################################
#########################################################################