We’ve been hearing for a long time that PowerShell (PS) is the wave of the future for SQL Server administration. Under the covers SSMS itself is powered by PS so there ought to be benefits but how do you get started. Everybody has seen how you can right-click a node in the SSMS Object Explorer and jump right in to PS.
But then what? 
Let’s consider a small administrative task. Fred creates a bunch of jobs without setting the job owner so he owns them all. He decides to seek greener pastures and when his account is dropped the jobs quit working.
I know you all have (or should have) a script for finding and changing the owner of certain SQL Agent jobs on a SQL instance. They all use a cursor (or something like it) to query msdb.dbo.sysjobs and step through the results, executing msdb.dbo.sp_Update_Job where appropriate. This certainly works but it’s kind of a kludge. Wouldn’t it be more intuitive to say something like “Look at all the jobs, where ‘Fred’ is the owner then change the owner to ‘sa’.”?
That’s really all you do in PS and you can find examples from Mr. Google. However, PowerShell’s ability to use shortcuts and abbreviations often makes figuring out what’s really happening like making sense of alphabet soup.
Here are two snippets of code. The first lists all the jobs on an instance with their owners and the second finds all the jobs with a certain owner and changes that owner to another. Let’s unpack each and see what’s going on.
dir | FT Name, OwnerLoginName –auto
“dir” is an alias for “Get-ChildItem” (Also abbreviated “GCI”). It functions like the “dir” command at the DOS prompt – it looks at the current location and returns everything “below”. The important difference is that it doesn’t return a string about the objects, it returns the objects themselves. Keep that in mind as we go on.
“|” The pipe character represents the PS pipeline which hands the object(s) to the next command. In our case we are passing all the objects returned by Get-ChildItem to the next command. See this link for more information on the pipeline.
“FT” is short for “Format-Table” and is used to format output. The comma separated arguments “Name” and “OwnerLoginName” tell FT which columns to return and the “-auto” automatically prettifies the output for human consumption.
Follow the above screenshots and paste this line of code into the PowerShell window and hit “Enter” to see it for yourself.
Now that we can see who owns each job lets change some owners.
dir | ?{$_.OwnerLoginName –eq “Fred”} | %{$_.OwnerLoginName = “sa”; $_.Alter(); $_.Name}
“dir|” functions as above but remember the actual objects are being passed, not just a property value (name, owner, etc.). In this case we don’t need all the jobs, just the ones Fred owns.
“?” is an alias for the “Where-Objects” commandlet which filters for the objects you want. The curly brackets (“{}“) contain the script block where you put the filter logic. “$_.” refers to each object coming through the pipe and “OwnerLoginName” is the attribute examined as it passes through the filter. “-eq” is the equality operator (=) while ‘“Fred”‘ is the string it is trying to match.
After this the objects that met the filter criteria are piped to the next command. Again we have an alias, “%“. This is short for “ForEach-Object” and allows you to iterate the objects and change properties or execute methods. In this case we will set (“=“) the “OwnerLoginName” value to the string “sa”. The “Alter()” is a method call that updates any changes. Finally the “Name” prints out the name of each job changed.
As you can see, PowerShell uses an intuitive and object oriented approach to working with SQL Server objects though sometimes shortcuts and aliases can make it a little hard for beginners to interpret. It’s well worth the time to take a careful look at any scripts you download so you know exactly what’s going on. PowerShell has a very nice help system that allows you to do this research. Look here if you’d like some help on Help.
Philergia 