Checks/Roles.Tests.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
param (
    [object]$config,
    [string]$SqlInstance,
    [PSCredential]$SqlCredential,
    [String]$Database
)

$roles = Get-DbaDbRole -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $database
$roleMembers = Get-DbaDbRoleMember -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $database
$permissions = Get-DbaUserPermission -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $database -IncludePublicGuest

ForEach ($case in $config.roles){
    Describe "Checking role $($case.rolename)" {
        It "$($case.rolename) Should Exist" {
            ($case.rolename -in $roles.name) | Should -BeTrue -Because "Role from config should be in db"
        }
    }

    Describe "Checking Role $($case.rolename) membership" {
        Foreach ($user in $case.members){
            It "$($user.username) Should be a member of $($case.rolename)" {
                ($rolemembers | Where-Object {$_.username -eq $user.username -and $_.role -eq $case.rolename}) | Should -BeTrue
            }
        }

        It "$($case.rolename) Should not contain members not defined in schema" {
            ($rolemembers | Where-Object {$_.role -eq $case.rolename -and $_.username -notin ($case.members.username)} | Measure-Object).count | Should -Be 0 -Because "No members should exist who aren't in the config "
        }
    
    }

    Describe "Checking Role $($case.rolename) permissions" {
        ForEach ($permission in $case.permissions) {
            It "Role $($case.rolename) Should have $($permission.permission) on $($permission.securable)" {
                ($permissions | Where-Object {$_.Grantee -eq $case.rolename -and $_.permission -eq $permission.permission -and $_.securable -eq $permission.securable -and $_.member -in $permission.member} | Measure-Object).count | Should -Be 1
            }
        }
    }
}