0

A user.sql file which contains

USE [database] GO -- Legacy users cleanup DROP USER IF EXISTS [dw]; etc...

is located at the repository: https://company.visualstudio.com/Database/_git/Azure?path=etc...

I have a powershell script running on a VM that restores the database and I need it to remove users from the newly restored db using the above user.sql file.

Is there a way to access the file directly from the .ps1 script, read the code from it and execute it?

If not, what would be the steps to make it work?

Thanks and regards.

1 Answer 1

0

Presumably you have permissions to clone this repo to the box from which you wish to execute your powershell script. In this case, assuming you have dbatools installed, your script would look like this:

Push-Location c:\path\to\repo
git pull
Invoke-DbaQuery -SqlInstance sql1 -Database MyDb -File user.sql

If you do not have permissions to clone this repository, you can create an azure pipeline to run this script if you have a friendly network admin to make sure your pipeline can run in a network segment with access to your SQL Server instance. Your pipeline.yaml file would look something like:

jobs:
  - job: Run Script
    steps:
      - checkout: self
      - task: PowerShell@2
        inputs:
          targetType: "inline"
          script: |
            Push-Location c:\path\to\repo
            $credential = [PsCredential]@{
                user="SqlUser"
                password="$(PIPELINE_SECRET)"
            }
            Invoke-DbaQuery `
                -SqlCredential $credential `
                -SqlInstance sql1 `
                -Database MyDb `
                -File user.sql
1
  • Thank you Peter for replying. So it looks like that in any case I have to copy the DROP User script to a local folder in order to run it? I was hoping to be able to avoid that part. Thanks again.
    – TheNixon
    Commented Dec 12, 2022 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.