SQL Server – Baten ICT

Conclusion

Remember, DENY overrules all other permissions. The customer could not connect because of one of her groups was denied access, even though her permissions were just fine. So we granted the correct permissions to the group, removed the customer’s individual login and after that, she was able to connect again.

GRANT CONNECT SQL TO [BATENICT\Marketing] AS [sa];

1	GRANT CONNECT SQL TO [BATENICT\Marketing] AS [sa];

Sometimes you want to check something in your SQL environment that’s not yet included in the dbachecks module. Luckily, it is quite easy to extend it’s functionality.

Let’s start by creating a file that will contain your own checks:

New-Item -Path c:\MyChecks\ContosoChecks.Tests.ps1 -ItemType File -Force

1	New-Item -Path c:\MyChecks\ContosoChecks.Tests.ps1 -ItemType File -Force

Now add the following to the newly created file:

$filename = $MyInvocation.MyCommand.Name.Replace(".Tests.ps1", "")

@(Get-Instance).ForEach{

    Describe "Contoso DBA Database Configuration" -Tags Contoso, $filename {
        Context -Name "DBA database on $psitem" {
            It "DBA database should be present" {
                (Get-DbaDatabase -SqlInstance $psitem -Database DBA | Measure-Object).Count |
                    Should -Be 1 -Because "It should be there"
            }

            It "DBA should contain sp_WhoIsActive" {
                ((Get-DbaDatabase -SqlInstance $psitem -Database DBA).StoredProcedures | Where-Object -Property Name -eq 'sp_WhoIsActive' | Measure-Object).Count |
                    Should -Be 1 -Because "It should be there"
            }
        }
    }
}

$filename = $MyInvocation.MyCommand.Name.Replace(".Tests.ps1", "")

@(Get-Instance).ForEach{

Describe "Contoso DBA Database Configuration" -Tags Contoso, $filename {

Context -Name "DBA database on $psitem" {

It "DBA database should be present" {

(Get-DbaDatabase -SqlInstance $psitem -Database DBA | Measure-Object).Count |

Should -Be 1 -Because "It should be there"

}

It "DBA should contain sp_WhoIsActive" {

((Get-DbaDatabase -SqlInstance $psitem -Database DBA).StoredProcedures | Where-Object -Property Name -eq 'sp_WhoIsActive' | Measure-Object).Count |

Should -Be 1 -Because "It should be there"

}

In the Describe block I added ‘Contoso’ to the Tags parameter. You can use this tag to include (or exclude) specific tests.

All that’s left to do is to register your custom repository:

Set-DbcConfig -Name app.checkrepos -Value 'c:\MyChecks\' -Append

1	Set-DbcConfig -Name app.checkrepos -Value 'c:\MyChecks\' -Append

Now you can invoke the checks by running:

Invoke-DbcCheck -SqlInstance localhost\SQL2017 -ComputerName localhost -Tags Contoso

1	Invoke-DbcCheck -SqlInstance localhost\SQL2017 -ComputerName localhost -Tags Contoso

Category: SQL Server

Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission.

Conclusion

Dbachecks: How to add a custom repository