delias
Posts: 1
Joined: Tue Jul 28, 2020 3:11 pm

SQL Script Replacement from custom action powershell property

Tue Jul 28, 2020 3:35 pm

Hi,

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;
SQL Statement

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;
Script Replacement
Find What: $AppPool_UserName$
Replace With: APPPOOL_USERNAME

Can you please advice on the correct way to do this?

Thanks!
Dani

Catalin
Posts: 6542
Joined: Wed Jun 13, 2018 7:49 am

Re: SQL Script Replacement from custom action powershell property

Wed Jul 29, 2020 5:33 pm

Hello Dani and welcome to our forums,
PowerShell (PS) inline script with deferred execution
Please note that it is not possible to set a property during in a custom action having its "Execution Time" of "When the system is being modified (deferred)".

Setting a property is only possible for custom actions having their "Execution Time" of "Immediately".

With that being said, could you please change the execution time of your custom action and let me know if everything works as expected?

Best regards,
Catalin
Catalin Gheorghe - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Building Installers”