T-SQL Tuesday #135: The outstanding tools of the trade that make your job awesome

Welcome to T-SQL Tuesday, the brainchild of Adam Machanic ( twitter ) and ward of Steve Jones ( blog | twitter ).
T-SQL Tuesday is a monthly blogging party where a topic gets assigned and all wishing to enter write about the subject.
This month we have Mikey Bronowski ( blog | twitter ) asking us about the most helpful and useful tools we know of or use.

Tools of the trade are a topic that I enjoy. I have a (sadly unmaintained) list of scripts from various community members on my blog. This list is not what I’m going to talk about though. I’m going to talk about what to do with or any scripts.

I want to talk about you as a person and as a community member. Why? Because you are the master of your craft and a master of their craft takes care of their tools.

Store Them

If you are using scripts, community-made or self-made, then you should store them properly. By properly, I’m talking source control. Have your tools in a centralised place where those who need it can access it. Have your scripts in a centralised place where everyone gets the same changes applied to them, where you can roll back unwanted changes.

Check out Brett Miller’s ( blog | twitter ) presentation “GitOps – Git for Ops people“.

Version Them

If you are using community scripts, then more likely than not, they are versioned. That way you’re able to see when you need to update to the newest version. No matter what language you’re using, you can add a version to them.

PowerShell has a ModuleVersion number, Python has __version__, and SQL has extended properties.

Or even take a page out of Bret Wagner’s ( blog | twitter ) book and try XML comments.

Take Care of Them

If you take care of these tools, if you store them, version them, and make them accessible to those who need them, then they will pay you back a hundredfold.
You’ll no longer need to re-write the wheel or pay the time penalty for composing them. The tools will be easy to share and self-documented for any new hires.
Like the adage says: Take care of your tools and your tools will take care of you.

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!

T-SQL Tuesday #127 – Non SQL Tips and Tricks

Words: 412

Time to read: ~ 2 minutes.

T-SQL Tuesday time! This month we have Kenneth Fisher ( blog | twitter ) as the host and he’s asking us for our non-SQL related tips and tricks.


I will confess to only starting this post late. So my tips and tricks will not be well thought out or planned. They will involve PowerShell though, something that I think about daily.

What we know

I consider it to be common knowledge that you can open up PowerShell from the explorer.

By default, my PowerShell opens up to “C:\Users\Shane”.

But by typing “PowerShell” into the location bar of an explorer, you can open a PowerShell session.

The PowerShell session will open to the location the explorer was open.

Et Viola

Reverse it

Did you know that you can drag and drop onto a PowerShell console?

Let’s create an empty text file.

New-Item -Name TestEmptyFile.txt -ItemType File

And we can see that it shows up in the open explorer location.

If we were to drag and drop the file into our PowerShell console window, it will return the full path to that file

Learn from History

If you spend a lot of time in a PowerShell console, it’s not rash to presume that you’re going to be running some of the same commands over and over again.

That’s where PowerShell’s history comes into play.

By using the command Get-History or even its alias h , you can see the commands that you’ve run before:


Claudio Silva ( blog | twitter ) mentions in his T-SQL Tuesday post about using PSReadline’s HistorySearchBackward and HistorySearchForward.

I’ve fallen into the habit of using #.

Get-History returns an Id that we can use with our #. On our PowerShell console, if we want to run the 2nd command in our history, we only need to type #2 and then press Tab.

If we don’t know the Id but know a word, phrase, or substring of the command we can use #<word | phrase | substring of the command> to look through our history for the command.

So to find the command Get-History that we ran, we can use #Hist and then press Tab.

If it’s still not the right command, we can keep pressing Tab until we find the previous command that we’re looking for.

..but Sweet

I’m pretty sure I haven’t blown your socks off in amazement with these tips and tricks. But they work, they’re semi-useful, and they should be helpful.

I hope you knock some use out of them.

Updating the Account Password for my Constrained Endpoint

Words: 448

Time to read: ~ 2 minutes

A while ago, I was asked about Automation and told a story about my friend James.

In that story I talked about Constrained Endpoints and how, by using them, I could do a take the best bits of automation & delegation and not have to worry about unlocking James anymore.

Well, I was wrong

A while after I created that Constrained Endpoint, I was greeted one day by James saying he was receiving a weird error when he tried to unlock his account.

Connecting to remote server ‘server_name’ failed with the following error message : The creation of a new
Shell failed. Verify that the RunAsPassword value is correctly configured and that the Group Policy setting “Disallow
WinRM from storing RunAs credentials” is Disabled or Not Configured. To enable WinRM to store RunAs credentials,
change this Group Policy setting to Disabled. For more information, see the about_Remote_Troubleshooting Help topic.

CategoryInfo : OpenError: (‘server_name’:String) [], PSRemotingTransportException
FullyQualifiedErrorId : CannotConnectRunAsFailed,PSSessionStateBroken

Unlock James has turned into Error James.

The fact that this occurrence came the day after I had reset my password, and the fact that the error message contained the words “[v]erify that the RunAsPassword value is correctly configured” was not something that was lost on me.

Luckily, PowerShell is fabulously easy to explore with it’s Get-Help command so it was a simple case to look for commands around Session Configurations – Get-Command -Name *Session*Configuration* – and look at the help contents of the Set-PSSessionConfiguration cmdlet.

Make sure you include proper help in your functions, it’ll help you immensely when you come back to it after some time.

Get-Help -Name Set-PSSessionConfiguration -Detailed
Also available by using help Set-PSSessionConfiguration -Parameter RunAsCredential

The Fix this Time

The evidence pointed to me needing to update the RunAsCredential of the endpoint and that was the overall fix for this issue.

Set-PSSessionConfiguration -Name UnlockJamesEndpoint -RunAsCredential (Get-Credential)

I threw in my username and my new password, did a quick test to see if the endpoint was available for me ( it was ), asked James to test that it was available for him ( it was ), and I closed off the ticket.

Aesop Out

Constrained Endpoints are not a technology that I am familiar with yet. It’s nice to know that I can take a look at the error messages, use some troubleshooting processes – check out the book “How to Find a Wolf in Siberia” by Don Jones ( blog | twitter ) – and figure it out.

Then again, the technology world is filled with new technologies and if you have a job where you know everything about your technology stack then congratulations to you.

For everyone else, get used to not knowing. Network, Search, Learn. You’ll be obliviously proficient in no time!

Learning C# methods in PowerShell don’t like -1

Trying to be smart getting me learned!

Words: 432

Time to read: ~ 3 minutes

It’s been a while…

…since I’ve written a blog post and there’s no better way to get back into something than to just start doing. Even if it’s just a throwaway, little post.

So here’s mine. Hope you enjoy it.

Don’t ask why…

… but recently I was trying in PowerShell  to split a string up into its individual characters. So, as an example, ‘SQL Server’ would become the ('S', 'Q', 'L', ' ', 'S', 'e', 'r', 'v', 'e', 'r') collection of characters.

I also wanted the character before it and after it as well…

This CharPrev CharNext Char
S e

The Easiest Way…

…that I found was to simply ask for the character a certain position (or index) in the string.

$String = 'SQL Server'
for ($i = 0; $i -lt $String.Length; $i++ ) {
'This Char' = $string[$i]
'Prev Char' = $string[$i -1]
'Next Char' = $string[($i +1) % $String.Length]
view raw UsingForSyntax.ps1 hosted with ❤ by GitHub

As you can see we had to add a little got’cha to our code: ($i +1) % $String.Length

This is because, without the modulo operator (what remains when we divide the numbers), PowerShell looks for the next index (10) and returns nothing since there is essentially nothing in index 10.

So we ask PowerShell what 10 modulo the length of the string is ( 10 % 10) and the remainder is 0. This way we can wrap back around to the start again!

However, coming from a database background…

…this may seem like a pretty simple exercise, especially since we know that PowerShell has a Substring method.

$String = 'SQL Server'
0..($String.Length 1) | ForEach-Object Process {
$String.Substring($_, 1)

However, what happens when we try and go backwards i.e. $String.Substring(-1, 1)?


Exception calling “Substring” with “2” argument(s): “StartIndex cannot be less than zero.


Try as I might…

…I couldn’t get any way that used “.whatever()” to work. Substring; nope, Chars; nada.

The moment that I passed in a -1 I just saw a sea of red.

Thankfully I’ve been frequenting the PowerShell slack channel lately and they were able to let me know why.

sifb [Nov 12th at 10:01 PM]
@Shane O’Neill I think the $array[-1] loop-around is a powershell convenience, and doesn’t exist in C# / the lower level .Net libraries

So there we go…

…even though we may be used to SubString, it doesn’t mean that it is the best way for us to go. This is technically a new language and there are going to be tips and tricks for doing things that we don’t yet know about!

Half the fun is rooting them out, finding them, and slowly, slowly watching your code improve and knowing why.

I would have written a shorter [post], but I did not have the time.


Table Column Differences with T-SQL and PowerShell – Part 2

If this was a horror movie, it would be called “The Differencing”…duh duh duh!

The original post for this topic garnered the attention of a commenter who pointed out that the same result could be gathered using a couple of UNION ALLs and those lovely set-based EXCEPT and INTERSECT keywords.

I personally think that both options work and whatever you feel comfortable with, use that.

It did play on my mind though of what the performance differences would be…what would the difference in STATISTICS IO, TIME be? What would the difference in Execution Plans be? Would there even be any difference between the two or are they the same thing? How come it’s always the things I tell myself not to forget that I end up forgetting?

I have no idea about the last one but at least the other things we can check. I did mention to the commentor that I would find this an interesting blog topic if they wanted to give it a go and get back to me. All I can say is – Sorry, your mail must have got lost in transit. I’m sure it is a better blog post that mine anyway.

If you’re going to do it…

For this test, we’re not going to stop at a measely 4 columns per table. Oh no! For this one we’re going to go as wide as we can.

With a recent post by Kenneth Fisher ( blog | twitter ) out about T-SQL FizzBuzz, I’m going to create two tables, both of which will have incrementing column names i.e. col00001, col00002, …, col1024. Table1 will have all columns divisible by 3 removed while Table2 will have all columns divisible by 5 removed.

See, FizzBuzz can be useful!

So our table creation scripts…

[code language=”SQL”]
CASE WHEN v.number = 0
— Change this to 02 the second run through
THEN N’CREATE TABLE dbo.TableColumnDifference01 (‘
ELSE N’ col’ + RIGHT(REPLICATE(‘0′, 8) + CAST(v.number AS nvarchar(5)), 4) + N’ int,’
FROM master.dbo.spt_values AS v
WHERE v.type = N’P’
— Change this to ‘% 5’ the second run through
v.number % 3 != 0
OR v.number = 0)

See Note

NOTE: When you copy and paste the results of this query into a new window to open it, it is going to fail. Why? Well the end of the script is going to be along the lines of colN int, and it needs to be colN int). Why is it like this? Well it was taking to damn long to script that out. Feel free to change this to work for you. Hey if you do, let me know!

Now, how I’m going to do test this, is run each method 3 times (PIVOT, UNION, and PowerShell), then measure the third run of each method. This is mainly as I want to get rid of any “cold cache” issues with SQL Server where the plan has to be compiled or the data brought into memory.

…do it Pivot

So first up is the Pivot method from the last blog post. In case you’re playing along at home (and go on, do! Why should kids get all the fun) here is the code that I’m running.

SELECT Pivot1.ColumnName,
CASE WHEN [dbo.TableColumnDifference01] = 1 AND [dbo.TableColumnDifference02] = 1
THEN 'Both'
WHEN [dbo.TableColumnDifference01] = 1 AND [dbo.TableColumnDifference02] IS NULL
THEN 'Table 1 only'
WHEN [dbo.TableColumnDifference01] IS NULL AND [dbo.TableColumnDifference02] = 1
THEN 'Table 2 only'
ELSE 'Eh…this should not really happen'
END AS HumanReadableFormat
c.[name] AS ColumnName,
1 AS ColumnExists
FROM sys.columns AS c
(OBJECT_ID(N'dbo.TableColumnDifference01', N'U'), 'dbo.TableColumnDifference01'),
(OBJECT_ID(N'dbo.TableColumnDifference02', N'U'), 'dbo.TableColumnDifference02')
) AS tb (ObjectID, TableName)
ON c.object_id = tb.ObjectID
) AS UnPivotedColumns
MAX(ColumnExists) FOR TableName IN ([dbo.TableColumnDifference01], [dbo.TableColumnDifference02])
) AS Pivot1
ORDER BY Pivot1.ColumnName ASC;

And here is our results:

Yup, those be columns

What we are really after though is the stats, execution plan and time to complete for our 3rd execution. Now as much as I love reading the messages tab for the stats information, I feel with blog posts that aesthetics is king, so I’m going to be using the free tool by Richie Rump ( twitter ) “Statistics Parser


Elapsed time: 00:00:00.136


Execution Plan:

Probably the first plan I’ve seen where the SORT isn’t the most expensive!

..do it UNION

Secondly we have what I dubbed “the UNION method” (no points for figuring out why) and the only change I’ve made to this script is to add in PARSENAME() and that’s only so that the script would..you know…work.

DECLARE @table1 nvarchar(50) = 'dbo.TableColumnDifference01',
@table2 nvarchar(50) = 'dbo.TableColumnDifference02';
SET @table1 = PARSENAME(@table1, 1);
SET @table2 = PARSENAME(@table2, 1);
'Yes' AS Table1,
'No' AS table2
) AS tbl1
'No' AS Table1,
'Yes' AS table2
) AS tbl2
'Yes' AS Table1,
'Yes' AS table2
) AS tbl12
) AS tblall

Results be like:

Yep, Yep, Yep, Yep, Nope, Yep…


Elapsed time: 00:00:00.624

hmm…less Scan Counts but 5 times the reads…also 5 times slower than the PIVOT method. Maybe the execution plan will be prettier?

Execution Plan:


Yeah…so…that’s…that’s different from the first plan! I was right in my comment though, there is a concatenation operator (there’s actually 2, you may need to zoom in to find them though)

…do it PowerShell

Finally we have the PowerShell method. No messing about here, let’s get straight to it! I’m going to lump all the code together in one gist and I’ll be wrapping it in Measure-Command to get the speed of the command.

$Table01Param = @{
ServerInstance = 'localhost\SQLServer2K16'
Database = 'Pantheon'
Query = "SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.TableColumnDifference01', N'U')"
$Table02Param = @{
ServerInstance = 'localhost\SQLServer2K16'
Database = 'Pantheon'
Query = "SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.TableColumnDifference02', N'U')"
$Table1 = Invoke-Sqlcmd @Table01Param
$Table2 = Invoke-Sqlcmd @Table02Param
Compare-Object ReferenceObject $Table1 DifferenceObject $Table2 Property ColumnName IncludeEqual |
Select-Object Property *,
@{label = 'HRF'; expression = {switch ($_.SideIndicator) {
'==' { 'Both' }
'=>' { 'Table 2 Only' }
'<=' { 'Table 1 Only'}
} |
Sort-Object Property ColumnName;
# Stats please — basically how long it takes
Measure-Command {
Compare-Object ReferenceObject $Table1 DifferenceObject $Table2 Property ColumnName IncludeEqual |
Select-Object Property *,
@{label = 'HRF'; expression = {switch ($_.SideIndicator) {
'==' { 'Both' }
'=>' { 'Table 2 Only' }
'<=' { 'Table 1 Only'}
} |
Sort-Object Property ColumnName;


Yeah I’m liking VS Code more and more…


Elapsed time: 00:00:00.249

help *execution*; help *plan*

Would you believe that I couldn’t figure out how to get an execution plan for PowerShell 🙂

If anybody knows, hit me up!

Finishing off

You know at the start of this, I was fully expecting the PowerShell to win out, followed by the UNION method, because it’s use of UNION, EXCEPT, and INTERSECT which are basically made for this kind of problem, and the PIVOT method bringing up a distant last since PIVOTs have this complexity stigma attached to them and what is complex is normally slow.

From a sheer speed point of view, the actual results are:

  1. Pivot
  2. PowerShell
  3. Union

Who knew!?

I don’t think this is the end of my use of PowerShell or Union operators though. I’m not going to replace all the stuff that I can with Pivots. For one I just think that PowerShell and the Union operators are just too cool!

I actually like this result for two reasons.

  1. There are multiple way to do something in SQL, there are good ways and better ways. The main point is whatever option you choose, make sure you know what it entails and can justify it.
    Whatever works for you, works for you!
  2. You don’t know something, test it and find out! What you think the outcome may be, may not be true.

Now if you’ll excuse me, I want to figure out if there’s a way to return execution plans with PowerShell.


My Function Won’t Accept Parameters? Get-Help!

Getting Get-Help Help

The following is a recounting of an issue that I had and how I went about resolving it. No computers were harmed in the making of this post.

Ask me for one PowerShell command that everyone should know and I can answer you: Get-Help.

Fairly descriptive name if you ask me. Today I’m focusing on using Get-Help selectively to help me figure out why my custom function just won’t accept parameters!

You say Test Case. I say Basket Case.

We are going to need a custom test function for the audience to play along with at home, luckily Shane’s got you covered.
This is a Tactical Estimation of Shane’s Test function – aka T.E.S.T. function; very simple but all the important parts are there.

[code language=”PowerShell”]
Function Test-FunctionByParameter {
[Parameter(Mandatory = $true,
ValueFromPipelineByPropertyName = $true)]
process {
"Success, I’m [$Parameter]"

If I’ve done my maths right, and I always do my maths right (as far as you know), then this function should take input from the pipeline and output it in the string “Success, I’m …”

Do I do my maths right?

[code language=”PowerShell”]
Get-Service -Name *sql* |
Select Name -first 1 |

It’s the “carrying the 1” that always gets me!

Huh, parameter problem.

I thought this was supposed to work like this. You pipe in information, magic happens, and functions work, no?

Well, when in doubt, Get-Help.


Before I go any further though, just so that everyone knows how to use Get-Help, I’m going to show you one of the secret techniques for using Get-Help.

[code language=”PowerShell”]
Get-Help *help*


Yup, I use dbatools

Why does help exist?

When you think about it, why is there even a function called help?
As far as I’m aware it’s basically the same as Get-Help except it automatically pipes the output to | more so we get pages rather than a wall of text.

Is there more that we can do with Get-Help though? Is there a way that we can return the examples only? Syntax only? Parameters only?

Is there not a way that we can do such things?!

Yessum, the Possums cousin

Okay I cheated on the first one; examples are pretty easy. PowerShell actually already takes care of that for you.

[code language=”PowerShell”]
Get-Help Get-Help -examples

Help me if you can I’m feeling examples…I mean down!

The other two, while not laid out for you as pretty as that, are not that difficult to do. What needs to be remembered about Get-Help is that it is a cmdlet. And what do cmdlets normally output?…

What?! No! Objects!
They normally output Objects! Wow…next time just pipe it to Get-Member if you don’t know.

I Object!

Now I first saw this done in a blog post by Adam Bertram ( blog | twitter ) but I do believe that it warrants further highlighting.

If you did pipe Get-Help to | Get-Member you would have seen a NoteProperty called syntax, so if we want the syntax for a cmdlet, we can specify that using:

[code language=”PowerShell”]
(Get-Help Get-Help).syntax

Syntax, useful for all languages

So for parameters we need…yup .parameters.

[code language=”PowerShell”]
(Get-Help Get-Help).parameters

Parameters…languages use them as well I guess

Hmm, not as handy as I thought it would be. What happens if we pipe that to Get-Member (Alias gm as I’m getting lazy here)?

[code language=”PowerShell”]
(Get-Help Get-Help).parameters | gm

Well lookie here, another NoteProperty!

Let’s try that and see what we get, shall we?

[code language=”PowerShell”]
(Get-Help Get-Help).parameters.parameter

…the exact same >:( Fine, have the same screenshot then!

It’s always brightest before the dawn

And the answer comes always before you smash your screen in rage.

If we pipe the above information to Get-Member again, we get more useful information this time (I’m not going to show it, you know how to pipe to gm by now).

This looks like something we can work with 🙂

I’m from a database background so can we make this pretty, all I care about is the name and the pipeline input.

[code language=”PowerShell”]
(Get-Help Get-Help).parameters.parameter |
Select-Object -Property name,pipelineinput

ByPropertyName…what’s that?

By Odin’s Beard! I mean PropertyName

You know one of these days I should really read this help file (you should too) because half way down the results of the following code is some interesting info…

[code language=”PowerShell”]
help about_pipelines


Cmdlets parameters can accept pipeline input in one of two different ways:

— ByValue: Parameters that accept input “by value” can accept piped objects
that have the same .NET type as their parameter value or objects that can be
converted to that type.

For example, the Name parameter of Start-Service accepts pipeline input
by value. It can accept string objects or objects that can be converted to

— ByPropertyName: Parameters that accept input “by property name” can accept piped
objects only when a property of the object has the same name as the parameter.

For example, the Name parameter of Start-Service can accept objects that have
a Name property.

(To list the properties of an object, pipe it to Get-Member.)

Some parameters can accept objects by value or by property name. These parameters are
designed to take input from the pipeline easily.

So that’s the problem?! The names need to match up! I can do that with Select-Object!

All I need to do is add a custom label using @{Label='<custom label>';Expression={'<custom expression>'}}


[code language=”PowerShell”]
Get-Service -Name *sql* |
Select-Object -First 1 -Property @{l=’Parameter’;e={$_.Name}} |

I always do my maths right!

So now when I run a command and get the crazy…

The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.

I can just run:

[code language=”PowerShell”]
(Get-Help <cmdlet name>).parameters.parameter |
Select-Object Name,pipelineInput

And know exactly where to fix! 🙂

SQL Server Configuration Manager: Where has it gone?

Why this blog post?

Now this is a short one.

A while back I was testing attempting to access SQL Server using a defined port number.

To do that, you have to access SQL Server Configuration Manager to specify the port number, or at least I think you do (If you don’t have to do it this way, please, oh please, let me know!).

So, since my laptop is running on Windows10, I open up the Start menu, type in “config” and…nothing!
No SQL Server Configuration Manager! The closest I got was the configuration manager for Reporting Server.

I’m shocked by this especially because when I type it into the Start menu now, I get…

Windows 10 making a liar out of me…

Ignoring the fact that it shows up in the result pane now, I had to go to MSDN and figure out where it’s default path is.

But WHY this blog post?

Well it turns out that, for me, the default path is now in…


I say “<nn>” because it’s a number dependant on what version of SQL Server that you are running.
For example, I have a 2012, a 2014 and a 2016 version on my laptop so I have 3 versions of SQL Server Configuration Manager.

I may have been msc-taken, get it? 🙂

Seriously, is that it?

Nope, that ain’t it.

Opening up Windows Explorer, going all the way down to that level just to get the config manager? Ain’t nobody got time for that!

They say “imitation is the greatest form of flattery” so taking ideas garnered from dbatools and the fact that I’m just finished a pre-con for SQL Saturday Boston in PowerShell from Mike Fal ( b | t ), thank you Andy Mallon ( b | t ) for pushing me out of my comfort zone even if it was only to just sign up!, I’ve created a little PowerShell script to let me choose and open a SQL Server Configuration Manager.

Check it out! And let me know what you think.

Script me!

[code language=”PowerShell”]
Get-ChildItem -Path C:\Windows\sysWOW64\ -Filter "*SQLServerManager*" |
Out-GridView -PassThru |


Seriously “OutGridView -PassThru” should get an award!

Now, I only have 2014 service up on this time so when Out-GridView pops up, I’m going to choose SQLServerManager12.msc and click “Ok”

This doesn’t count as a GUI!

Which will open up our “missing” configuration manager!

I may have just been doing something stupid though…


PowerShell! Helping make my life easier since…whenever I actually figure it out. 😐

Why I Powershell my Laptop off

Could be my shortest blog post so far…


Kalen Delaney ( blog | twitter ) has an excellent blog post about Windows Fast Startup and, while I’m not going to repeat what she has said here because, like I already mentioned, it’s an excellent post and I encourage you to read it ( and maybe give her a lil’ subscribe 😉 ), what I will mention is that I encountered this feature with my new laptop and had it interfering with my SQL Server testing (again read her post as to possible causes why).

Using Powershell for documenting Replication had me wondering if there was a way I could get around this using Powershell. So while this is another post that is not about SQL Server, it is about Powershell.

Hey, at least I’m consistent in my consistencies.

What’s the Problem?

A quick lmgtfu, brought me to the following page and command:

[code language=”powershell”]
shutdown /s

Which pops open a window saying the computer will shutdown and, after a delay, that’s what it does.

At this stage I’ve read enough documentation to know that
shutdown /s
doesn’t follow the standard Verb-Noun convention of Powershell and that delay was slightly annoying.

Plus, everyone raves about the Get-Help commandlet so I figured I would try that.

[code language=”powershell”]
Get-Help *shutdown*

Gave me a list of commands and one of them seemed to fit what I wanted.

[code language=”powershell”]
Get-Help Stop-Computer;



3 things here.

  1. You now know how I turn my computer off all the time
  2. It’s amazing what you can do with Powershell, and
  3. Kalen says

    So you might already know, but I didn’t know, until I learned it, of course.

I didn’t know, but found a work-around so didn’t learn it.
I’d advise you to follow Kalen’s approach (as I’m going to try from now on) but, hey, at least you now know mine.