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.
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.
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 😐
Let’s create a new user with no permissions in SQL Server.
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?
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!
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.
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.
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…
The Easiest Way…
…that I found was to simply ask for the character a certain position (or index) in the string.
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!
Time to read: ~ 5 minutes
Update 2021-07-14: Marked code blocks as preformatted
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…
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('')
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.
And here is our results:
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“
..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.
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!
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:
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.
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.
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…
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.
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).