Tag Archives: Logins

Write Your first Pester Test Today

Posted on by

I was in Glasgow this Friday enjoying the fantastic hospitality of the Glasgow SQL User Group @SQLGlasgow and presenting sessions with Andre Kamman, William Durkin and Chrissy LeMaire

I presented “Green is Good Red is Bad – Turning your checklists into Pester Tests”. I had to make sure I had enough energy beforehand so I treated myself to a fabulous burger.

20171110_114933-compressor.jpg

Afterwards I was talking to some of the attendees and realised that maybe I could show how easy it was to start writing your first Pester test. Here are the steps to follow so that you can  write your first Pester test

Decide the information you wish to test
Understand how to get it with PowerShell
Understand what makes it pass and what makes it fail
Write a Pester Test

The first bit is up to you. I cannot decide what you need to test for on your servers in your environments. Whatever is the most important. For now pick one thing.

Logins – Lets pick logins as an example for this post. It is good practice to disable the sa account is advice that you will read all over the internet and is often written into estate documentation so lets write a test for that

Now we need the PowerShell command to return the information to test for. We need a command that will get information about logins on a SQL server and if it can return disabled logins then all the better.

As always when starting to use PowerShell with SQL Server I would start with dbatools if we run Find-DbaCommand we can search for commands in the module that support logins. (If you have chosen something none SQL Server related then you can use Get-Command or the internet to find the command you need)

find-dbacommand.png

Get-DbaLogin . That looks like the one that we want. Now we need to understand how to use it. Always always use Get-Help to do this. If we run

Get-Help Get-DbaLogins -detailed

we get all of the information about the command and the examples. Example 8 looks like it will help us

get-dbalogin example

So now try running the command for our disabled sa account

Get-DbaLogin -SqlInstance rob-xps -Login sa -Disabled

disabled sa account

So we know that if we have a disabled sa account we get a result. Lets enable the sa account and run the command again

not disabled.png

We don’t get a result. Excellent, now we know what happens for a successful test – we get one result and for failed test we get zero results. We can check that by running

login count

The first one has the account disabled and the second one not. So now we can write our Pester Test. We can start with a Describe Block with a useful title. I am going to add a context block so that you can see how you can group your tests.

describe context

and then we will write our test. Pester Tests use the It keyword. You should always give a useful title to your test

it should

Now we can write our test. We know that the command returns one result when we want it to pass so we can write a test like this

login test.png

The code I have added is

(Get-DbaLogin -SqlInstance rob-xps -Login sa -Disabled).Count | Should Be 1
which is
  • the code for getting the information about the thing we wanted to test (The count of the disabled sa logins on the instance)
  • a pipe symbol |
  • The Should key word
  • The Be keyword
  • and the result we want to pass the test (1)

Ta Da! One Pester test written. You can run the test just by highlighting the code and running it in VS Code (or PowerShell ISE) and it will look like this for a passing test

passing test

It is better to save it for later use and then call it with Invoke-Pester

invoke

So now you can write your first Pester test. Just find the PowerShell to get the information that you need, understand what the results will be for passing and failing tests and write your test 🙂

Getting the Latest Version of the Module

The magnificent Steve Jones wrote about getting the latest version of Pester and the correct way to do it. You can find the important information here

Spend a Whole Day With Chrissy & I at SQLBits

If you would like to spend a whole day with Chrissy LeMaire and I at SQLBits in London in February – we have a pre-con on the Thursday
You can find out more about the pre-con sqlps.io/bitsprecon
and you can register at sqlps.io/bitsreg

Checking for SQL Server logins with PowerShell

Posted on by

As some of you may know, I love PowerShell!

I use it all the time in my daily job as a SQL DBA and at home whilst learning as well.

Not only do I use PowerShell for automating tasks such as Daily Backup Checks, Drive Space Checks, Service Running Checks, File Space Checks, Failed Agent Job Checks, SQL Error Log Checks, DBCC Checks and more but also for those questions which come up daily and interfere with concentrating on a complex or time consuming task.

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I keep them all in a functions folder and call them whenever I need them. I also have a very simple GUI which I have set up for my colleagues to enable them to easily answer simple questions quickly and easily which I will blog about later. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays question which I often get asked is Which database does this account have access to?

This question can come from Support Desks when they are investigating a users issue, Developers when they are testing an application as well as audit activities. It is usually followed by what permissions do they have which is covered by my next blog post.

I start by getting the list of servers from my text file and creating an array of logins for each domain as I work in a multi domain environment

image

Then loop through each server and if the login exists write it out to the window.

image

I then repeat this but loop through each database as well

image

A little bit of formatting is added and then a quick easy report that can easily be copied to an email as required.

To call it simply load the function

image

and get the results

image

The code is below

<#
.Synopsis
   A workflow to display users server and database logins across a SQL estate
.DESCRIPTION
    Display a list of server login and database user and login for SQL servers listed 
 in sqlservers.txt file from a range of domains
    AUTHOR: Rob Sewell http://sqldbawithabeard.com
    LAST UPDATE: DATE:07/01/2015
.EXAMPLE
   Show-SQLUserLogins DBAwithaBeard

   Shows the SQL Server logins and database users matching DOMAIN1\DBAWithaBeard,DOMAIN2\DBAWithaBeard, DBAWithaBeard
#>

Workflow Show-UserLogins
{
    
param ([string]$usr)
$servers = Get-Content '\\hdat01\ICTS\ICTS\DBA-SAP\sql\Powershell Scripts\sqlservers.txt'
$ErrorActionPreference = "SilentlyContinue"

# Create an array for the username and each domain slash username

$logins = @("DOMAIN1\$usr","DOMAIN2\$usr", "DOMAIN3\$usr" ,"$usr" )

Write-Output "#################################" 
Write-Output "SQL Servers, Databases and Logins for `n$logins displayed below " 
Write-Output "################################# `n" 

#loop through each server and each database and display usernames, servers and databases
Write-Output " Server Logins`n"

foreach -parallel ($server in $servers)
    {
    inlinescript
        {
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Using:server
        if(!$srv.Version)
            {
            Write-Output "$Using:server is not contactable - Please Check Manually"
            }
        else
            {              
            foreach ($login in $Using:logins)
                {      
                if($srv.Logins.Contains($login))
                    {
                    Write-Output " $Using:server -- $login " 
                    }           
                else
                    {
                    continue
                    }
                } 
            }         
        }
    }
Write-Output "`n###########################"
Write-Output "`n Database Logins`n"
foreach -parallel ($server in $servers)
    {
    inlinescript
        {
        $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Using:server
        if(!$srv.Version)
            {
            Write-Output "$Using:server is not contactable - Please Check Manually"
            }
        else
            {                                                                
            foreach($database in $srv.Databases|Where-Object{$_.IsAccessible -eq $True})
                {
                foreach($login in $Using:logins)
                    {
                    if($database.Users.Contains($login))
                        {
                        Write-Output " $Using:server -- $database -- $login " 
                        }
                    else
                        {
                        }   
                    }
                }
            }
        }
    }    
Write-Output "`n#########################################"
Write-Output "Finished - If there are no logins displayed above then no logins were found!"    
Write-Output "#########################################"  
}