We’re moving our forums to a new platform.

You can still browse and search existing topics, but you can no longer create new topics or post replies.

For questions, technical issues, or feature requests, please don’t hesitate to contact our support team at support@advancedinstaller.com.

Thank you for your understanding!

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

SQL Script Replacement from custom action powershell property

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: 7853
Joined: Wed Jun 13, 2018 7:49 am

Re: SQL Script Replacement from custom action powershell property

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”