Reading from .sql file located in Visual Studio repository

Question

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.

Answer 1

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