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.
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.
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.
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];
GO
CREATE LOGIN LimitedPermissions WITH PASSWORD = N'MorePermissionsMoreProblems!';
GO
Now let’s test it out. I have a database in my instance called __DBA. Can we access the users in that database?
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.
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.
USE __DBA;
GO
CREATE USER LimitedPermissions FROM LOGIN LimitedPermissions;
GO
And now let’s try our Get-DbaDbUser command again.
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:
#Hashtag
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.
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.
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.
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!
…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 Char
Prev Char
Next Char
S
r
Q
Q
S
L
L
Q
L
S
S
e
e
S
r
r
e
v
v
r
e
e
v
r
r
e
S
The Easiest Way…
…that I found was to simply ask for the character a certain position (or index) in the string.
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!
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.
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”]
SELECT TOP (1024)
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,’
END
FROM master.dbo.spt_values AS v
WHERE v.type = N’P’
AND (
— Change this to ‘% 5’ the second run through
v.number % 3 != 0
OR v.number = 0)
FOR XML PATH(”)
[/code]
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.
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”
Stats:
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.
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')"
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:
Pivot
PowerShell
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.
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!
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.
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.
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 …”
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.
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.
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.
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:
…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.
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
[/code]
METHODS OF ACCEPTING PIPELINE INPUT
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
strings.
— 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>'}}
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.
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…
Well it turns out that, for me, the default path is now in…
C:\Windows\sysWOW64\SQLServerManager”<nn>”.msc
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.
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.
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.
You now know how I turn my computer off all the time
It’s amazing what you can do with Powershell, and
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.