I am trying to create a database user on a specific database with an inline SQL Script using script replacement to set the appropriate username from a property that is retrieved using Custom Actions PowerShell (PS) inline script with deferred execution. I have read the user guide for script replacement and for setting a property value from a PS script but can't seem to get it working.
PS Script
Code: Select all
Import-Module WebAdministration;
$identity = Get-Item -Path "IIS:\AppPools\MyCustomAppPool\" | Select-Object -ExpandProperty processModel;
AI_SetMsiProperty APPPOOL_USERNAME $identity.userName;
Code: Select all
SET @SQLStatement =
N'IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = ''$AppPool_UserName$'')
BEGIN
CREATE USER [$AppPool_UserName$] FOR LOGIN ' + @user + N' WITH DEFAULT_SCHEMA=[dbo]
ALTER ROLE [db_owner] ADD MEMBER [$AppPool_UserName$]
END'
EXEC sp_executesql @SQLStatement;
Find What: $AppPool_UserName$
Replace With: APPPOOL_USERNAME
Can you please advice on the correct way to do this?
Thanks!
Dani