ci_MininalPermissions.yml

name: DBA Dash CI with Minimal Permissions

on: 
    push:
    workflow_dispatch:

jobs:
  build:
    name: Build
    runs-on: windows-latest

    strategy:
      fail-fast: false
      matrix:
        language: [csharp]

    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Setup .NET Core
        uses: actions/setup-dotnet@v4
        with:
          dotnet-version: 8.0.x
          dotnet-quality: 'ga'
      
      - name: Check SDK version
        run: dotnet --list-sdks

      - name: Build
        run: dotnet build -c CLI

      - name: Build GUI
        run: dotnet build DBADashGUI -o DBADashBuild\DBADashGUIOnly

      - name: Add msbuild to PATH
        uses: microsoft/setup-msbuild@v2
        
      - name: Build DB
        run: msbuild dbadashdb -property:Configuration=CLI

      - name: Get Version
        id: GetVersion 
        shell: powershell
        run: | 
          $path = [System.IO.Path]::Combine((Get-Location),"DBADashBuild\CLI\DBADash.dll")
          $version = [System.Reflection.Assembly]::LoadFrom($path).GetName().Version
          $version.ToString(3)
          Write-Output "BUILD_NUMBER=$($version.ToString(3))" >> $env:GITHUB_OUTPUT

      - name: Get Build Reference
        id: GetBuildReference
        shell: powershell
        run: | 
                Invoke-WebRequest -Uri 'https://dataplat.github.io/assets/dbatools-buildref-index.json' -OutFile 'DBADashBuild\CLI\BuildReference.json'

      - name: Zip
        shell: powershell
        run: | 
          $zipPath = "DBADash_${{steps.GetVersion.outputs.BUILD_NUMBER}}.zip"
          Compress-Archive -Path "DBADashBuild\CLI\*" -DestinationPath $zipPath
          $guiZipPath = "DBADash_GUI_Only_${{steps.GetVersion.outputs.BUILD_NUMBER}}.zip"
          Compress-Archive -Path "DBADashBuild\DBADashGUIOnly\*" -DestinationPath $guiZipPath

      - name: Install SQL
        uses: potatoqualitee/mssqlsuite@v1.7
        with:
          install: sqlengine
          collation: Latin1_General_BIN

      - name: Check SQL Install
        run: | 
          sqlcmd -S localhost -U sa -P dbatools.I0 -d tempdb -Q "SELECT @@version as Version;"
          sqlcmd -S localhost -U sa -P dbatools.I0 -d tempdb -Q "SELECT SERVERPROPERTY('Collation') AS Collation;"

      - name: Create Identity Test DB
        run: | 
          sqlcmd -S localhost -U sa -P dbatools.I0 -d tempdb -Q "CREATE DATABASE TestIdentity"
          sqlcmd -S localhost -U sa -P dbatools.I0 -d TestIdentity -Q "CREATE TABLE dbo.IdentityTest(ID TINYINT IDENTITY(1,1));DECLARE @i INT=1; WHILE @i <250 BEGIN INSERT INTO dbo.IdentityTest  DEFAULT VALUES; SET @i+=1; END"

      - name: Add SQL Login for DBA Dash service with minimal permissions
        run: | 
          sqlcmd -S localhost -U sa -P dbatools.I0 -d master -Q "CREATE LOGIN [DBADashService] WITH PASSWORD=N'Use_A_Str0ng_Passw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
          GRANT CREATE ANY DATABASE TO [DBADashService] /* Only required to allow service account to create repository database */
          GRANT VIEW SERVER STATE TO [DBADashService]
          GRANT VIEW ANY DATABASE TO [DBADashService]
          GRANT CONNECT ANY DATABASE TO [DBADashService]
          GRANT VIEW ANY DEFINITION TO [DBADashService]
          GRANT ALTER ANY EVENT SESSION TO [DBADashService] /* Required if you want to use slow query capture */
          USE [msdb]
          IF NOT EXISTS(SELECT * 
                FROM msdb.sys.database_principals
                WHERE name = 'DBADashService')
          BEGIN
            CREATE USER [DBADashService] FOR LOGIN [DBADashService]
          END
          ALTER ROLE [db_datareader] ADD MEMBER [DBADashService]
          ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [DBADashService]"

      - name: Configure & Install DBA Dash as Service
        shell: powershell
        run: | 
          $ErrorActionPreference = "Stop" 
          $zipPath = "DBADash_${{steps.GetVersion.outputs.BUILD_NUMBER}}.zip"
          $InstallPath = "C:\DBADashTest"
          "Extract to $InstallPath"
          Expand-Archive -Path $zipPath -DestinationPath $InstallPath -Force -ErrorAction Stop
          Set-Location $InstallPath
          "Configure"
          ./dbadashconfig -c "Data Source=localhost;UID=DBADashService;pwd=Use_A_Str0ng_Passw0rd;Initial Catalog=DBADashDB_GitHubAction;Encrypt=True;TrustServerCertificate=True;" -a SetDestination
          ./dbadashconfig -c "Data Source=localhost;UID=DBADashService;pwd=Use_A_Str0ng_Passw0rd;Encrypt=True;TrustServerCertificate=True;" -a Add --PlanCollectionEnabled --SlowQueryThresholdMs 1000 --SchemaSnapshotDBs "*" --NoWMI
          "Install Service"
          ./DBADashService install --localservice
          "Start Service"
          net start DBADashService
          "Wait 60 sec"
          Start-Sleep -s 60

          "Get Logs"
          $logsFolder = "$InstallPath\Logs"

          Get-ChildItem -Path $logsFolder | Get-Content

          exit 0

      - name: Wait 5min
        shell: powershell
        run: | 
          "Wait 5min"
          Start-Sleep -s 300

      - name: Output CollectionErrorLog
        shell: powershell
        run: | 
          Invoke-Sqlcmd -ServerInstance $params.ServerInstance -Database "DBADashDB_GitHubAction" -Query "SELECT * FROM dbo.CollectionErrorLog" -TrustServerCertificate | Format-Table

      - name: Output Table Counts
        shell: powershell
        run: | 
          ./Scripts/Get-TableCounts -ServerInstance "LOCALHOST" -Database "DBADashDB_GitHubAction" 

      - name: Run Pester Tests
        shell: powershell
        run: |     
          Install-Module Pester -Force -SkipPublisherCheck
          Import-Module Pester -PassThru
          $NoWMI=$true
          Invoke-Pester -Output Detailed Scripts\CI_Workflow.Tests.ps1
      
      - name: Output Log and Check for Errors
        shell: powershell
        run: | 
          ./Scripts/Get-LogContent -LogPath  "C:\DBADashTest\Logs" -ThrowError