T-SQL Tuesday #164: Code That Made You Feel A Way

Time to read: ~ 4 minutes

Words: 899

Welcome to T-SQL Tuesday, the monthly blogging party where we receive a topic to post.

This month we have Erik Darling ( blog ) asking us to post about “code that makes [us] feel a way“.

A while back, I was tasked to performance tune some code that brought me through all five stages of grief. It’s best to take you through the events from the developer’s viewpoint. There’s less cursing that way.


Hey, what’s up? You were asked to troubleshoot some code. Not a big deal; these requests come in from time to time. 

You have code that keeps coming up as a high consumer of CPU on our systems? OK?

It had gotten to the stage where it kept appearing in sp_WhoIsActive? What’s that? And should it not be “whom is active”? Well, agree to disagree.

Let’s see the code so. Wow, that’s a small scroll bar! Yeah, that’s one of ours.

No, it is. I swear it is.

I’m not surprised you can’t find it in stored procedures; we dynamically create it. Here’s the procedure. Yeah, all 900 lines! Pretty cool, huh?

What do you mean, why? We had to! We had to react to the different parameters that get passed in.


Alright, alright! Calm down. I misspoke.

Yeah, that’s it, breathe. There aren’t different parameters. It’s a parameter. Singular.

No, we still need to generate the query dynamically. Cause the XML could be different. Huh? Yeah, the parameter is XML; it could be anything in there. So we pass the XML in as a nvarchar(max) string.

You spilt some of your coffee there when you banged the desk.

Then we use sp_xml_preparedocument at the start, but then we have to use sp_xml_removedocument at the end.

You’ve never heard of those before? I thought you were a DBA?

We use the “prepare” to get the XML into a state that’s easier to consume, and we use the “remove” so we don’t get memory leaks!

Your face is getting a bit red, did you know that?

It’s SQL Server; it uses enough memory it can share some!

Did we read the docs? No, why? It can use one-eighth of the total memory available for SQL Server. Ah, but that’s “can”, not “will”.

Yes, yes, cursors upon cursors upon cursors. Why? We don’t know how many values are in each XML node in the params, so we have to split them out.

We then join them into a comma-delimited string, which is then used in IN clauses… woah! Is that the sound of your teeth grinding? You know that’s not good for your health. What do you mean neither am I?

Anyway, then we parse all of that down to a massive, what do you call it, swiss-army knife, lego-block, dynamic query built based on what’s passed in. You don’t call it that? I thought you did. What do you call it so? Wow, that’s not a word I had heard of before.

It’s not too bad, though! We pass in everything as literal values, so it’s faster that way. We read up on that parameter sniffing issue you said can happen. That isn’t going to catch us out here!

Modern problems require modern solutions, as they say. What’s that? It’s not a modern problem? I’m a what? Are you allowed to call me that?


You want me to re-write it? You’ll help? It’ll take a lot of time. It’s not really a priority.

Plus, it’s not even worth it. How much CPU? Is that a “illion” with an “m” or “illion” with a “b”? Per run? Is that a lot? Well, I really wouldn’t know, now would I? 

Yeah, good point; you’ll also have to check how much memory it uses. Hey! Probably one-eight of the total memory available for SQL Server. Alright, it wasn’t that bad a joke; everyone’s a critic!

You’ll give me credit? But it’s fine in Dev; there’s no point. I suppose we could remove some of the WHERE OR IS NULL branches since we know if they’re empty when we create the query.

Nah, that would make the procedure a bit too big. Anything over 1000 lines is too much. Well, 985 is still less than 1000!


Why are you crying? No, we care, we do, we really do. No, not all the code is like this; this is old. Yes, we stopped using that XML parameter pattern. Yes, and the cursors. Well, most of them, at least. 

Your tears are mixing with the coffee you spilt, you know that? 

Here, there’s really no need for the fetal position in the middle of the office. You have yet to see the code for the busier databases.

You know what, why don’t you go home and come back in tomorrow, and I’ll go over that way, OK?


Hey! There’s our favourite DBA. You seem a lot cheerier.

Oh, you don’t have to perf-tune that query anymore? That’s great; see, it wasn’t so bad. What’s that? The latest release failed? It ran fine in Dev.

You’re permitted to set up a QA environment to test performance before code gets released? What kind of code? Any stored procedure?

Ah, that’s OK. No, really, it’s fine. We started using ORMs and embedding SQL into the app a while ago. This won’t affect us.

You’ve started crying again.

T-SQL Tuesday 157 – End of Year Activity

Words: 544

Time to read: ~ 2 minutes


Welcome to T-SQL Tuesday, the monthly blog part where we are given a topic and asked to blog about it. This month we have Garry Bargsley, asking us to discuss end-of-year activities.


My current workplace has a change freeze in place for the end of the year. Usually, I would say that this is risk-averse.
But seeing as the nature of the business (payments) means that the Thanksgiving/Black Friday/Christmas time is the busiest time of the year, I’m willing to cut them some slack.

So, what to do when we cannot deploy to production? Oh, we’ll still be busy! There are always management-approved fixes that get through, annual processes to complete, and project planning that has to be…well, planned.

But, my priority for this end-of-year is documentation.


We have a few different tools for documentation. Examples are Confluence, Google Sheets, Google Docs, etc.

But most of the time, documentation takes the form of scripts saved to source control.

These scripts are multiprocess and cross-team dependent and can quickly end up like me doing DIY. One hand trying to steady the nail, the other wielding the hammer, and the whole situation collapsing into swear words and tears.

We can’t currently have a “hit-Enter-and-leave-it” bunch of scripts because we have to stop midway for another team’s work or to check the results of what we’ve just run.


If we used Notebooks, this would be so much easier. I could create the code, save a snippet of the results to the notebooks, and then future executors could see what to expect.


We don’t use Notebooks.

Plain .sql files for me, it is! 

To ease the documentation burden and have some semblance of tidiness, I created a PowerShell tool that splits it out “all pretty like”.


Now, with a combination of Read-Clipboard from the ImportExcel module, I can grab results and turn them into a text table that I can add back into the script.

Simple example: we want to save the database name, create date, and compatibility level of the databases on an instance.

… I said simple example – not good or useful example.

	database_name = name,
FROM sys.databases;
Picture shows an SSMS screen selecting name, create_date, and compatibility level from sys.databases. Returning 5 rows

Now, let’s copy and paste the results into a comment on that script as a form of “documentation”.

Picture shows an SSMS screen selecting name, create_date, and compatibility level from sys.databases. Returning 5 rows. Underneath the query in a comment are the pasted results that all have different spacing and are not aligned

Now, this time I’m going to copy those results, and run the following code in my PowerShell Core window, before pasting into the comment block.

Read-Clipboard | Format-TextTable | Set-Clipboard
Picture shows an SSMS screen selecting name, create_date, and compatibility level from sys.databases. Returning 5 rows. Underneath the query in a comment are the pasted results that are wrapped in a text table format and aligned under their header name

Given the option, I know which one I’d choose.


You can pick up the source-code here.

Feel free to check it out and add/remove from it.


I am aware that I could spend minutes of my time to evenly align the spaces. And, yes, I know that SSMS can do find-and-replace using simple Regex.
But if I manually structure the output every time, for all different types of columns & data types, with different spacings…

I’d be better off arguing with the company to start using Notebooks.

Until then, this works for me. Hopefully, it will work for you.

And hey, maybe it will help improve your documentation in the New Year. That has a higher chance of happening than my one to improve my DIY skills.

T-SQL Tuesday #155 – Write to Read, Not to Run

Words: 861

Time to read: ~ 5 minutes

Welcome to T-SQL Tuesday, the monthly blogging party where we talk about a topic given to us by the host. This month, we have Steve Jones (blog | twitter) asking us about Dynamic SQL.

There are a myriad number of uses for Dynamic SQL – I’ve already read some of the published posts and I’m impressed with the range. (Yeah, I’m writing this late; I’m aware).

I’m aiming for something different. I want to talk about the things I believe Dynamic SQL should have. You can disagree with me if you’d like; I’d welcome it (as long as you can justify it) but here are my thoughts on writing Dynamic SQL.


To make a contrived example, I’ve taken Andy Mallons (blog | twitter) script to return SQL Agent Job statuses, and converted it to Dynamic SQL

@job_sql AS nvarchar(max),
@job_name AS nvarchar(128) = N'syspolicy_purge_history', = N'No existy',
@debug_mode_on AS bit = 1; 0;
@nl AS nchar(2),
@actual_job_name AS nvarchar(128);
/* Newline for formatting */
SET @nl = NCHAR(13) + NCHAR(10);
/* Job existence check */
IF @job_name IS NOT NULL
SET @actual_job_name = (
FROM msdb.dbo.sysjobs
[name] = @job_name
IF @actual_job_name IS NULL
DECLARE @err_msg AS nvarchar(max);
SET @err_msg = FORMATMESSAGE(N'Cannot find any job labelled: %s', @job_name);
RAISERROR(N'%s', 0, 1, @err_msg) WITH NOWAIT;
SET @job_sql = N'USE msdb;
is_running = CASE
WHEN ja.job_id is NOT NULL AND ja.stop_execution_date IS NULL
last_run_time = ja.start_execution_date,
next_run_time = ja.next_scheduled_run_date,
last_job_step = js.step_name,
job_outcome = CASE
WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL
THEN N''Running''
WHEN jh.run_status = 0
THEN N''Failed''
WHEN jh.run_status = 1
THEN N''Succeeded''
WHEN jh.run_status = 2
THEN N''Retry''
WHEN jh.run_status = 3
THEN N''Cancelled''
FROM dbo.sysjobs AS j
LEFT JOIN dbo.sysjobactivity AS ja
ON ja.job_id = j.job_id
AND ja.run_requested_date IS NOT NULL
AND ja.start_execution_date IS NOT NULL
LEFT JOIN dbo.sysjobsteps AS js
ON jh.job_id = js.job_id
AND js.step_id = ja.last_executed_step_id
LEFT JOIN dbo.sysjobhistory AS jh
ON jh.job_id AND j.job_id
AND jh.instance_id = ja.job_history_id;'
/* Add filter: job_name */
IF @actual_job_name IS NOT NULL SET @job_sql = CONCAT(
@job_sql, @nl,
j.[name] = @ds_job_name'
/* Add sorting */
SET @job_sql = CONCAT(
@job_sql, @nl,
ja.start_execution_date DESC;
IF @debug_mode_on = 1
RAISERROR(N'%s%s', 0, 1, @job_sql, @nl) WITH NOWAIT;
EXECUTE [master].[sys].sp_executesql
@stmt = @job_sql,
@param1 = N'@ds_job_name AS nvarchar(128)',
@ds_job_name = @actual_job_name;
view raw JobStatus.sql hosted with ❤ by GitHub

I was going to apologise for how long and messy it looked but I realised that’s par for the course with Dynamic SQL.


There is this maxim that I’ve heard bandied about regarding code:

Code is read much more often than it is written[…]


There should be an addendum on that quote for DBAs:

DBAs troubleshoot Dymanic SQL more often than they write it

Probably others, but definitely me

Saying that, Dynamic SQL should have these items to help with that effort.

Proper Formatting

It’s extremely easy to write Dynamic SQL so that it comes out in one string. A hodge-podge of plus signs, variable assignments, and red text that sometimes it seems like a foreign coding language

>++++++++[<+++++++++>-]<.>++++[<+++++++>-]<+.+++++++..+++.>>++++++[<+++++++>-]<+ +.------------.>++++++[<+++++++++>-]<+.<.+++.------.--------.>>>++++[<++++++++>–

The above code sample is apparently a working “Hello World” program in one of those languages.

Don’t do this, properly format your Dynamic SQL. It will help when the code shows up in your monitoring toolkits. You have them (and know how to use them), right?

To help check your formatting, Dynamic SQL should include…

A Debug Method

Troubleshooting is so much easier when you know what you are going to run. The amount of Dynamic SQL where you have to build that in your head while reading the code is ridiculous!

If you give a procedure filled with Dynamic SQL to a junior DBA, then you’re going to see how fast fear and resignation fills someones face.

It’s straightforward to create a debug method that shoots out the code that is going to be run. Secondary benefit is it ensures that you format your code properly because you can see how it is going to turn out

IF @debug_mode_on = 1
	RAISERROR(N'%s%s', 0, 1, @job_sql, @nl) WITH NOWAIT;
	EXECUTE [master].[sys].sp_executesql
		@stmt = @job_sql,
		@param1 = N'@ds_job_name AS nvarchar(128)',
		@ds_job_name = @actual_job_name;


There are a few other things that I like to add to Dynamic SQL but I will grant are not necessary. I’ll leave them to you to make up your own minds about.

Sanitise inputs

If the user passes in an object, ensure it’s there

/* Job existence check */
IF @job_name IS NOT NULL
	SET @actual_job_name = (
		FROM msdb.dbo.sysjobs
			[name] = @job_name

	IF @actual_job_name IS NULL
		DECLARE @err_msg AS nvarchar(max);

		SET @err_msg = FORMATMESSAGE(N'Cannot find any job labelled: %s', @job_name);
		RAISERROR(N'%s', 0, 1, @err_msg) WITH NOWAIT;

Escaping input

Same point really – if you want to raise a warning or error with what is passed in, use something that escapes user input.

I tend to use FORMATMESSAGE for these bits.


These might appear to be overkill but I have an cough contrived cough example; the code I took from Andy’s github and converted to Dynamic SQL!

Quick Test with Debug

If we pass in no job name and leave debug mode on; it splits out the code sans any WHERE clause before the ORDER BY to bring us back all jobs:

If we change up the job name to one that does not exist e.g. @job_name = N'No existy'; then we verify that the job doesn’t exist and error out:

Passing in a job that does exist, then adds that job name to the WHERE clause (parameterised, of course):

Let’s Run It!

Turn off debug mode and…

That’s throwing up an error message – but I’d be confident in saying that anyone glancing over the code in the gist would miss the three mistakes.

Yes, three of them – I wrote one on purpose but created two more by accident.

Luckily we can turn debug more back on, grab the output, and parse it in a new window:

The AND on the JOIN clause was intentional – the terminating semi-colon before the WHERE clause was not, neither was the mistake on the alias. The joys of re-writing code.

Fix these mistakes up in our Dynamic SQL, turn debug mode back off, and ready to re-run? Probably took around 5 minutes going slow, but now when we re-run the code:

Result Set

That’s one example of why I have believe Dynamic SQL should have proper formatting, and debugging, and some small others.

I had more examples but they normally come to me around 02:00 in the morning while I’m on swearing and sweating on an incident call.

I’d prefer to fix and forget them, after I make sure they don’t happen again.

Attempting SUM() OVER () in PowerShell

Words: 891

Time to read: ~ 5 minutes


Like most things in life, this piece of work came about while attempting to complete something else. It’s not a bad thing, I expect it at this stage.

Easy Like Sunday Morning

I find it easy to get the total of a row in SQL. Hell, when it is not particularly important, I’ll even go the easy route and use a calculated column in the table.

CREATE TABLE dbo.PushupsOctober
	pushup_date date NOT NULL
	attempt_01 tinyint NULL,
	attempt_02 tinyint NULL,
	attempt_03 tinyint NULL,
	attempt_04 tinyint NULL,
	attempt_05 tinyint NULL,
	attempt_06 tinyint NULL,
	attempt_07 tinyint NULL,
	attempt_08 tinyint NULL,
	total_pushups_per_day AS (ISNULL(attempt_01, 0) + ISNULL(attempt_02, 0) + ISNULL(attempt_03, 0) + ISNULL(attempt_04, 0) + ISNULL(attempt_05, 0) + ISNULL(attempt_06, 0) + ISNULL(attempt_07, 0) + ISNULL(attempt_08, 0))

Then, all I have to do is insert the data and SQL will automatically take care of calculating the total per row for me.

INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/07/2020 00:00:00', 20, 20, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/08/2020 00:00:00', 20, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/09/2020 00:00:00', 20, 20, 25, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/10/2020 00:00:00', 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/11/2020 00:00:00', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/12/2020 00:00:00', 25, 25, 25, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/13/2020 00:00:00', 20, 15, 15, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/14/2020 00:00:00', 30, 30, 20, 20, 25, 20, 20, 20);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/15/2020 00:00:00', 25, 25, 25, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/16/2020 00:00:00', 25, 25, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/17/2020 00:00:00', 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

FROM	dbo.PushupsOctober;
Why count when not need to?

Once you have the total per row, you throw in a SUM(that total) OVER () and you have a grand total. Thank you to Kevin Wilkie ( blog | twitter ) for re-igniting my curiosity about Window Functions again.

		SUM(p.total_pushups_per_day) OVER () AS total_so_far 
FROM	dbo.PushupsOctober AS p;
Total total

Easy Like Monday Morning

PowerShell is a different beast. Please don’t get me wrong; I still love the language. I don’t find it easier to get a row total and then a grand total though.

It’s possible! I’m just hoping that there is a better way. Saying all that here is my attempt at a row total and grand total using PowerShell.

If you have a better way (you choose the conditions that satisfy “better”) please let me know.

Grabbing the Data

First, let’s grab the data from the table in our database.

$data_2 = Invoke-DbaQuery -SqlInstance localhost -Database LocalTesting -Query @'
SELECT * FROM dbo.PushupsOctober;

Removing Unwanted Properties

Here’s where I remembered that I had a calculated column, realised that it would be cheating to use it and decided it needed to go. Thankfully, this also enabled me to get rid of those pesky columns that get returned from Invoke-DbaQuery when you forget the parameter -As PSObject!

$data_2 = $data_2 | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors, total_pushups_per_day

Grabbing Property Names

There’s a couple of things that we need here. We need a way to add up all the “attempt” columns so we need a way to select them all.

$props = $data_2[0].PSObject.Properties | Where-Object Name -like 'attempt*' | Select-Object -ExpandProperty Name

There, that should do nicely!

Grabbing the Values for those Properties

Now, we can iterate over them and get all the values.

foreach ($p in $props) { $data_2[0] | Select -ExpandProperty $p }

Potential Problem

See all those empty lines? Yep, that’s a potential problem for Measure-Object.

$hasToBeAnEasierWay = foreach ($p in $props) { $data_2[0] | Select -ExpandProperty $p }
$hasToBeAnEasierWay | Measure-Object -Sum

Removing NULL or WhiteSpace

Thankfully, there’s a way to get rid of those empty lines.

$hasToBeAnEasierWay | Where-Object { -Not [String]::IsNullOrWhiteSpace($_) } | Measure-Object -Sum

Putting it ALL Together

Now that we have the skeleton of a script ready, let’s put it all together.

Row total

$data_2 | ForEach-Object -Begin {
    $props = $data_2[0].PSObject.Properties | Where-Object Name -like 'attempt*' | Select-Object -ExpandProperty Name
} -Process {
    $total = $null
    $hasToBeAnEasierWay = $null

    $hasToBeAnEasierWay = foreach ($prop in $props) {
        $_ | Select-Object -ExpandProperty $prop
    $total = ($hasToBeAnEasierWay | Where-Object { -Not [String]::IsNullOrWhiteSpace($_) } | Measure-Object -Sum).Sum

    $_ | Select-Object -Property *, @{
        Name = 'total_per_day'
        Expression = { $total }
    }, @{
        Name = 'days_left'
        Expression = { ((Get-Date -Date '2020-10-31') - (Get-Date -Date $_.pushup_date)).Days }
} -OutVariable data_3 | Format-Table -Autosize

We now have the row total in our total_per_day property. And, with our use of -outvariable data_3, we have the results saved into a variable called $data_3 .

Grand Total

Once we have a single column that we can sum up to give us our grand total, then PowerShell makes this operation trivial.

I do have to use Format-List here because Format-Table can’t fit all the properties in so our new property total_so_far won’t show up.

$data_3 | Select-Object -Property *, @{
    Name = 'total_so_far'
    Expression = { ($data_3 | Measure-Object -Property total_per_day -Sum).Sum }
} | Format-List

There We Go!

While, I’d argue that it’s not as easy as SQL, it’s completely possible to get row totals and grant totals in PowerShell.

Honestly though, I hope there’s an easier way. Otherwise, I’m going to do it in SQL and then grab it out into PowerShell afterwards.

T-SQL Tuesday #131: Data Analogies, or: Explain Databases Like I’m Five!

Words: 906

Time to read: ~ 5 minutes

Welcome to T-SQL Tuesday! This month’s host is Rob Volk (blog|twitter) and the topic is Data Analogies.


Come in! Come in, my boy!
Now, your father sent you to me to explain what I used to do.
Well, I was a DBA, my boy!
What do you mean “what does that do”? I took care of the databases! I made sure that they were looked after, that they had vim and vigour, and that they didn’t go down.

What? No, I don’t mean “down-down”, it’s not a direction! I mean that they didn’t go offline. No, not “off-off”, well not quite… It was my duty to ensure that they were available. Got it? No?

Database Administration

Well, let’s take that whiskey cabinet over there Jas…Jaeysin. Let us say that the whiskey cabinet is our database, and it is our job to see that it is never empty. We do that by lots of ways; by checking on it and making sure that nothing is alarming, like empty bottles. We check that there is space if someone wants to add more whiskey and that anyone who wants something from it, can get it. Like me, hehe.

What? You don’t understand how that’s like being a DBA? Well think about it my boy, I would check on the databases, make sure nothing was alarming, and that the data was always available for whoever wanted it.


What’s that? You want some? Ho ho, my boy, you are one for jests. I tell you what, try opening the cupboard door on the left. Yes, the one where you can see all the sticks and cherries through the glass. Not a problem for you, was it? Put back the cherry, please. And wipe your hands…NOT ON THE GLASS!
Nevermind, my boy, nevermind, I shouldn’t have put the soda water in a dangerous place like that…inside a cupboard…away from the ledge. Try and open the right cupboard door now. Yes, the one with the fancy bottles and the shiny lights. Yes, I’m aware it’s locked. Now see, you cannot open that door but I can because I have permission to open that door.
That was another part of my job, making sure that people had the right permission to get what they wanted, and that people without permission could not.

What’s that? Who decides on the permissions? Well, back then, it was a business decision. Where those higher up in standing would pick, and I would have to follow their instructions. Now, I’m in charge.

What do you mean that’s not what your father says? Right, well, I’ll be having a few words with him, and we’ll see more about this “under his partner’s thumb” business. No, I can’t open it either. Because I don’t have the key. Yeah well, I may be db_owner, but not sysadmin… Nevermind.


What else did I do? Well, I made sure those who wanted data from the database knew where they could get the data and could get the data promptly.
Well, do you see the whiskey cabinet? Yes, the one on the right. Yes, the one you’re not allowed open. Yes, you’re allowed to look…oi! Quit your cheek, or I’ll add that to the list of things I’ll have to talk to your father about.
Now, if someone were to go to that cabinet wanting a nice Scotch, they only have to reach into the middle shelf, and they would have a whole choice of Scotch to choose from. I know that because I know that my middle shelf is only for Scotch.
Same with the databases; I helped people to create tables to hold their similar data the same way I choose that shelf to have my Scotch.

And see the way that the bottles get fancier as you scan your eyes from left to right. I ordered that shelf, so the most expensive bottles were all the way over there on the right. And, the least expensive bottles are all over to the left.
Same with the databases; I would create indexes so that people could go to what they wanted, whether that be the “expensive bottles” or not.
No more looking through the entire table for what they want, they knew exactly where to go and knew when they could stop looking as well.

What? Indexes, my boy, an index. No, it hasn’t nothing to do with your finger! Wait! You may be on to something there. Tell me, have you ever run your index finger down a phone book? A phone book. You’ve never heard of it? Hold on…this thing? Ever seen this? Big yellow book, lots of information? Yes, I know we have Google, nevermind.


Was that it? No lad, that was not it. The world of data is vast and open. So much more than what an analogy using a whiskey cabinet can provide. But I will leave you with one more analogy; Redundancy. It’s always essential to have a redundant copy of your data, a recovery plan if a disaster were ever to strike.
Open that desk drawer for me, my boy. Yes, it’s another bottle of whiskey. A redundant backup, if you would. Now I believe I see your father pull into the drive, so it is time for you to leave. I shall go see if my backup can still be restored.

Goodbye, you little disaster.

Minimum Permissions for Get-DbaDbUser

Words: 806

Time to read: ~ 4 minutes

Update: 2020-07-15 – Thank you Garry Bargsley for being an unofficial editor 🙂

Update: 2020-07-17 – Thanks to Shawn Melton for spot-checking this and letting me know ALL permissions needed!

All Users:
A user on the database with ALTER ANY USER permission.

Current User and System Users:
To work against all databases for the current user and system users requires CONNECT ANY DATABASE.



Thanks to Shawn Melton for pointing out that CONNECT ANY DATABASE allows the user to see only themselves and the system users.

To see all users from Get-DbaDBUser, the caller will need a user on the databases and the permissions ALTER ANY USER.


Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
    Group-Object -Property Database

4 records are returned – the user itself and the system users.



CREATE USER LimitedPermissions FROM LOGIN LimitedPermissions;

GRANT ALTER ANY USER TO LimitedPermissions;

The LimitedPermissions login now has a user in the database and we’ve granted that user the ALTER ANY USER permission.

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
    Group-Object -Property Database

Now, we can see all the users; the user itself, the system users, and the other user I created on the database.

Original Article

The Backstory

Work is in the process of automating tasks. Part of this automation includes verifying the automation that we’ve done.

Where am I going with this?

Well, when we’ve automated the creation of database users we also want to verify that we’ve created the users that we say we’ve created.

My fellow co-workers have, thankfully, seen the dbatools light and we use the command Get-DbaDbUser to get the users in a database and compare the list against the users we were supposed to create.

If there are any users that should have been created but don’t show up, well then we have a problem.

The Principle of Least Privilege

Works fine for me […] but it looks like […] can’t run it with her “public” access to the db server.

I’m not going to sugarcoat things – the person that sent me the request has more access than they rightly need. The “public” access worker did not need any of that access so I wasn’t going to just give her the same level.

Plus, we’re supposed to be a workforce that has embraced the DevOps spirit and DevOps is nothing if it doesn’t include Security in it.

So, if I could find a way to give the user enough permission to run the command and not a lot more, then the happier I would be.

But, I was surprised how difficult it was to find out what permissions were needed to run Get-DbaDbUser. Even more surprised when I failed and realised I’d have to find out myself.

If anyone else can Google/Bing it and get the answer, please let me know 😐

The Test

Let’s create a new user with no permissions in SQL Server.

USE [master];

CREATE LOGIN LimitedPermissions WITH PASSWORD = N'MorePermissionsMoreProblems!';

Now let’s test it out. I have a database in my instance called __DBA. Can we access the users in that database?

    Username = LimitedPermissions
    Password = 'MorePermissionsMoreProblems!'
Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException

It doesn’t work. What’s even more surprising is that it silently doesn’t work. No warnings, no permissions errors, or nothing. And I included the -EnableException switch!

The Investigation

It’s good to know that you can check out the contents of the dbatools (and other) commands from PowerShell. No, I’m not talking about opening the .ps1 files. I’m talking about using the Function:\ psdrive.

Get-ChildItem -Path Function:\Get-DbaDbUser |
    Select-Object -ExpandProperty Definition

See those $server.databases and $db.users? For me, that means that it’s using SMO (Server Management Objects). If there was any hope of me google/binging permissions before this, well it’s gone now.

The Will is going

To cut a rather long story short, eventually I came to the idea of thinking that maybe it only needs to connect to the database. So let’s try that.


CREATE USER LimitedPermissions FROM LOGIN LimitedPermissions;

And now let’s try our Get-DbaDbUser command again.

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
    Select-Object Database, Name, LoginType, UserType


Let’s try all of the databases on the instance now

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -EnableException -Verbose |
    Group-Object -Property Database
Oh it has the system databases as well now!

Apart, from the system databases (excluding model) it only works on __DBA.

Give it all

Now, let’s use the CONNECT ANY DATABASE server permission.

USE [master];

And we’ll run against all databases again.

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -EnableException -Verbose |
    Group-Object -Property Database

Sin é

That’s it! Minimum permissions that I could find for Get-DbaDbUser is the permission to connect to the database.

Hope that helps!

Retrospective: Speaking at DataScotland

Words: 315

Time to read: 1.5 minute

Data Scotland

On the 13th of September 2019, I spoke at DataScotland; my first time talking at a data conference.

My quasi-clickbait title was Feel Validated with dbachecks. If you guessed that I was talking about dbachecks then you’re right.

This is a brief retrospective of that time. Thinking back on that time still makes me relive the emotions that I felt. Nervousness, excitement, and panic.

Good times!


This was my first time speaking at a conference as well as my first time attending Data Scotland.

I recommend that you check it out. It’s an amazing conference created by passionate people and staffed by dedicated volunteers.

The Good

What didn’t count as the good is the way to put this!

  • Amazing fellow speakers.
  • Getting to meet other first-time speakers.
  • Talking with the volunteers.
  • Speaking with attendees.
  • Seeing people who I hadn’t seen in a long time.

Thank you to Craig Porteous, Louise Paterson, Paul Broadwith, and Robert French for all your work and encouragement.

Thank you as well to Brent Miller, Andrew Pruski, David Alcock, and John McCormack for help with the presentation.

The Bad

Feeling drained.

I don’t put this down to DataScotland though.
You may not have heard from me for the last month. I felt drained and took time off from public exposure.

A full year of constant working on the day job and personal work.
2 conferences a month on average for the last year.
Spreading myself out on projects about SQL, PowerShell, Containers, Python, AWS, and Azure without rest.
It’s not something I could sustain without factoring in sharpening the axe time.


I’m easing myself back into things again with the caveat of planning ahead and making sure I don’t overwhelm myself.
First thing on my list, planning for DataScotland next year.

Whether it’s speaking, volunteering, or attending, I’ll be there.

T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests

The Good, the Bad, and the Bug-ly.

The Good, the Bad, and the Bug-ly.

This month’s T-SQL Tuesday is brought to us by Brent Ozar ( work | personal | tweets ) and has to do with SQL Server Bugs and Enhancement requests on Microsoft Connect.

Now the reason that this is the topic of this month’s T-SQL Tuesday is nicely spelled out by the host himself:

Now, more than ever, Microsoft has started to respond to Connect requests and get ’em fixed not just in upcoming versions of SQL Server, but even in cumulative updates for existing versions.

If you’ve kept an ear to the ground and an eye out on the social media, (seriously get on Twitter if you haven’t already; with #sqlhelp and #sqlfamily, you’ll never be alone, stuck, and/or bored again), you’d realise that this is true. Microsoft has engaged more than ever with it’s audiences, from hiring a PowerShell developer to actively engaging in various Trello Channels. So much so that a twitterbot was created to keep track of Connect items closed as fixed (Closed As Fixed) by MVP Chrissy LeMaire ( blog | tweets ).

Shameless Plug:

Now, I happen to have a Connect item and I’m going to link to it here. I can do this as this blog shares a commonality with Lesley Gore’s birthday party song. (It’s mine and I can cry if I want to)

The Good…

First of all, lets see what we’re running on here…




I really should be doing these against SQL Server 2016… 🙁

First contender: Inserting to an indexed view can fail

What would happen if I told you that, with regards to a view, sometimes inserting into the table could fail? Well that’s what this Connect item from Dave_Ballantyne found, along with the reason.

    Id integer not null,
    InView char(1) not null,
    SomeData varchar(255) not null

    LEFT(SomeData, CHARINDEX('x', SomeData) - 1) AS leftfromx
    InView ='Y'




Now that we have the groundwork laid out, it’s time for our insert attempt:

DECLARE @id integer,
@inview char(1),
@Somedata char(50)

SELECT @id = 1,
@inview = 'N',
@Somedata = 'a'

INSERT INTO myTable(Id, InView, SomeData)
SELECT @id, @inview, @Somedata


Do you see a LEFT() or SUBSTRING() ANYWHERE there???

He’s even gone so far as to give an explanation as to why this happens!

This is due to the compute scalar being executed prior to the filter.

Taking a look at the estimated execution plan, we can see that he’s right!

Let’s do it first, then see if we should have!

Imagine trying to troubleshoot what was causing the inserts to fail? Horrible! I can imagine tearing my hair out at that one!

I have this done as “The Good” just for the fact that, not content to just report the bug, Dave_Ballantyne even went so far as to find the possible cause. Now it’s just up to Microsoft to get to the fixing…

…The Bad…

Next up, we have moody31415 with “Can’t create Materialized View that references same table twice


CREATE TABLE test (A int)
SELECT t1.A, t2.A as A2
FROM dbo.test T1
JOIN dbo.test T2 ON T1.A=T2.A

The problem occurs when we try to add a unique clustered index on this bad boy:

on dbo.vTest (A, A2)


Ever get those times where you just can’t stand yourself?

Now, I originally put this down as “The Bad” because I thought that the issue could be down to trying essentially index the same column twice but that’s not the case…

CREATE TABLE dbo.B (col1 tinyint, col2 tinyint)
CREATE VIEW dbo.SecondOne
SELECT t1.col1, t2.col2
FROM dbo.B AS t1
JOIN dbo.B AS t2 ON t1.col1 = t2.col1
ON dbo.SecondOne (col1, col2)


…I mean After-A-Massive-Meal-Cant-Stand-Yourself

In the end, the reason that I have this in “The Bad” section is that I went to the documentation and read this part…

The SELECT statement in the view definition must not contain the following Transact-SQL elements: […] self-joins […]

Now it’s unknown whether this was there when this Connect item was created, but it’s there now (and I didn’t have enough time to re-plan this blog post)

…and The Bug-ly.

In my quest through the magical lands of Connect I stumbled across this little beauty of a bug by Anatoly V. Popov that I had to mention as “The Bug-ly”.

Altering indexed view silently removes all indexes

Yes, I know it’s the same as my one but dammit if I don’t think it’s a bug! That’s why it’s getting my “The Bug-ly” title.
This leaves me with a bit of a conundrum though…Do I close mine and comment on this one to get re-opened? Or do I try and continue to push on my one?

To be honest I don’t know which option to choose but, for anyone new to SQL Server, filing a Connect Item is an exciting but nerve-wracking time.
Exciting because you’ve worked with SQL Server enough to think you know how something should be done.
However, it’s nerve-wracking because you haven’t worked with SQL Server for long enough to know if you are just whining or have an actual item to be taken seriously.

Finding this Connect item was a small little shot of validation for me. Just a little something to say “Hey, I thought this too and I’m with you”.


It’s a great thing that Microsoft have started to respond to the Connect Items again and going through them you realise how different people are using SQL Server in ways that you didn’t even think to imagine.

So check out a few, leave your comment and up-vote/down-vote as you see fit.

Just…just don’t be this guy please