How to parameterize logins when executing SQL from PowerShell command

Question

How do we run CREATE LOGIN ... against a variable string?

I'm executing the following command:

Invoke-Sqlcmd -ServerInstance $ServerInstance -Database 'master' -Username $DBAdminAccount -Password $DBAdminPassword -Variable $SqlVariables -InputFile $ArtifactStagingDirectory\MasterDBSetup.sql

MasterDBSetup.sql:

....
    IF NOT EXISTS (SELECT name  FROM sys.sql_logins WHERE name = '$(SvcRRRComparisonUser)')
    BEGIN 
        CREATE LOGIN '$(SvcRRRComparisonUser)' WITH PASSWORD = '$(RRRComparisonUserPassword)'
    END
....

How do we run CREATE LOGIN ... against $(SvcRRRComparisonUser) ?

gotqn · Accepted Answer · 2020-12-02 20:08:16Z

2

In the $SqlVariables:

$SqlVariables = @("SvcRRRComparisonUser='user1',RRRComparisonUserPassword='pass1'")

Then in the SQL file:

IF NOT EXISTS (SELECT name  FROM sys.sql_logins WHERE name = $(SvcRRRComparisonUser)
BEGIN 
    CREATE LOGIN $(SvcRRRComparisonUser) WITH PASSWORD = $(RRRComparisonUserPassword)
END

answered Dec 2, 2020 at 20:08

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to parameterize logins when executing SQL from PowerShell command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related