Hi,
I’m using here-string inside a powershell script to create a multi-line sql script to be executed shortly after I create a database.
The relevant section is as follows and values have been altered:
[string] $SqlUserName = ‘MYUSERNAME’
[string] $DatabaseName = $OctopusParameters[‘targetDatabaseName’]
$ownershipchange = @"
use [$($DatabaseName)];
ALTER AUTHORIZATION ON DATABASE::$($DatabaseName) TO [sa];
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = N’$($SqlUserName)’)
BEGIN
CREATE USER [$($SqlUserName)] FROM LOGIN [$($SqlUserName)];
END
IF NOT EXISTS (SELECT 1 FROM sys.database_role_members rm INNER JOIN sys.database_principals u ON rm.member_principal_id = u.principal_id
INNER JOIN sys.database_principals r ON r.principal_id = rm.role_principal_id WHERE u.[name] = N’$($SqlUserName)’ AND r.[name] = N’db_owner’)
BEGIN
ALTER ROLE [db_owner] ADD MEMBER [$($SqlUserName)];
END
"@
Write-Host $ownershipchange
What ends up appearing on the deployment log is the following:
use [MyDatabaseDEV];
ALTER AUTHORIZATION ON DATABASE::MyDatabaseDEV TO [sa];
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = N’’)
BEGIN
CREATE USER [] FROM LOGIN [];
END
IF NOT EXISTS (SELECT 1 FROM sys.database_role_members rm INNER JOIN sys.database_principals u ON rm.member_principal_id = u.principal_id INNER JOIN sys.database_principals r ON r.principal_id = rm.role_principal_id WHERE u.[name] = N’’ AND r.[name] = N’db_owner’)
BEGIN
ALTER ROLE [db_owner] ADD MEMBER [];
END
Essentially, the values I wanted to substitute turned into a empty string whenever they’re enclosed by single quotes or braces. Am I not properly escaping some characters? Thanks.
