Add-VmUserToDatabase

Add-VmUserToDatabase.sql

/** Run this script on all Configuation and Colletion Databases **/

/** Change variables to name of VM needing Access to Azure DB **/
DECLARE @vmName1 NVARCHAR(20) = 'AzDO-01'

/** Declare query variables **/
DECLARE @query1 NVARCHAR(MAX),
        @query2 NVARCHAR(MAX),
	@query3 NVARCHAR(MAX)

/** Update @vmName1 **/
SET @query1 = CONCAT('CREATE USER [', @vmName1, '] FROM EXTERNAL PROVIDER')
SET @query2 = CONCAT('ALTER ROLE [db_owner] ADD MEMBER [', @vmName1, ']')
SET @query3 = CONCAT('ALTER USER [', @vmName1, '] WITH DEFAULT_SCHEMA=dbo')

/** Execute query variables **/
EXEC sp_executesql @query1
EXEC sp_executesql @query2
EXEC sp_executesql @query3

/**
	CREATE USER [@vmName] FROM EXTERNAL PROVIDER
	ALTER ROLE [db_owner] ADD MEMBER [@vmName]
	ALTER USER [@vmName] WITH DEFAULT_SCHEMA=dbo
**/