Month: March 2020

While doing some work on my materials for SqlBits training day I started thinking about a few of the problems with managing SQL Server permissions.

How easy it to audit them? If someone asks you the DBA exactly who has access to object A, can you tell them? How do people get access to that object, is it via a role, a schema or an explicit permission?

Is that information in an easy to read or manipulate manner?

How do you ensure that permissions persist between upgrades? I’ve certainly seen 3rd party upgrades that have reset database level permissions. Do you have a mechanism to check every permission and put them back as they were?

We’re all doing the devops these days. Our database schema is source controlled, and we’re deploying it incrementally in pipelines and testing it. But are we doing that with our database security?

So in the classic open source way, I decided to scratch my own itch by writing something. That something is dbaSecurityScan, a PowerShell module that aims to offer a solution for all of the above.

The core functionality atm allows you to export the current security state of your database, based on these 4 models:

Role based
User based
Schema based
Object based

You can also return all of them, or a combination of whatever you need.

At the time of writing, getting the security information and testing it is implemented, and you can try it out like this:

	Import-module DssSecurityScan

	–Create config and test against a new database
	$config = New-DssConfig -SqlInstance server1\sql2017 -Database oldProd
	Invoke-DssTest -SqlInstance server2\sql2019 -Database newProd -Config $config

	–Save a config, and then use it audit after a change
	New-DssConfig -SqlInstance server1\sql2017 -Database oldProd -ConfigPath ~/git/dbSourceControl/permissions.json
	Start-Sleep -Seconds 10080
	$config = ConvertFrom-Json -InputObject (Get-Content ~/git/dbSourceControl/permissions.json -raw)
	Invoke-DssTest -SqlInstance server2\sql2019 -Database newProd -Config $config

view raw dbaSecurityScanBasics.ps1 hosted with ❤ by GitHub

If you’ve got any suggestions for features, or want to lend a hand then please head over to dbaSecurityScan and raise an issue or pull request respectively 🙂

Working around sqlcmd on Mac OS issues

By Stuart Moore

On March 27, 2020

In Uncategorized

So I’m busy working on my new dbaSecurityScan module, busily trying to write all the pester tests for the appveyor pipeline

Part of the testing for the module involves building a number of test scenarios, each of which of which needs a database spinning up for it.

On the Windows builds, that’s nice and easy as I just use this in the test script to loop through all the scenarios and run the .sql files :

	ForEach ($file in (Get-ChildItem "$PSScriptRoot\scenarios" -File -Filter "*.sql" -recurse)){
	(& sqlcmd -S "$sqlInstance" -U "sa" -P "Password1!$£$" -b -i "$($file.fullname)" -d "master")
	}

view raw gistfile1.txt hosted with ❤ by GitHub

However, I’m developing on my Macbook Pro as I want this module to be nicely platform agnostic. SQL Server is running nicely in docker and dbatools can connect to it happily. But I just can’t get sqlcmd to work, lots of TCP Provider: Error code 0x102 and TCP Provider: Error code 0x2AF9 messages no matter what I try. So being short on time I though I’d try something else, and hopefully this will work when I get Linux build running as well

The scripts I want to run create databases, users, schemas and whole lot more, so there’s lots of batch separators (; and GO) in them.Invoke-DbaQuery is a great function, but it doesn’t like working with batch separators (which is not through want of trying, it’s just really tricky). So time to drop back to some raw SMO to run the scripts in with ExecuteNonQuery()

	if ($script:IgnoreSQLCMD) {
	$srv = Connect-DbaInstance -SqlInstance $script:appvSqlInstance -SqlCredential $script:appvSqlCredential
	ForEach ($file in (Get-ChildItem "$PSScriptRoot\scenarios" -File -Filter "*.sql" -recurse)) {
	$c = Get-Content $file -Raw
	$srv.Databases['master'].ExecuteNonQuery($c)
	}
	} else {
	ForEach ($file in (Get-ChildItem "$PSScriptRoot\scenarios" -File -Filter "*.sql" -recurse)){
	(& sqlcmd -S "$script:appvsqlInstance" -U "sa" -P "Password12!" -b -i "$($file.fullname)" -d "master")
	}
	}

view raw gistfile1.txt hosted with ❤ by GitHub

I’ve added a variable to my builds that lets me pick when I want to run via SQLCMD or via SMO. At the minute I’m just using it on my machine, but if I run into problems later, it’s nice to now I can just toggle by looking at which platform it’s running.

Musings of a Data professional

Month: March 2020

dbaSecurityScan – A new PS module for SQL Server security

Working around sqlcmd on Mac OS issues