Category Archives: Powershell

Security Theater

The biggest question I get about the Minion products is about security. People constantly tell me that a shop can’t implement Minion because it enables xp_cmdshell, whereas the SQL Server Maintenance Solution by Ola Hallengren doesn’t, so it’s naturally more secure. So in this post I’m going to show you, using both Minion and Ola’s routines, that what most people consider “more secure” is really just security theater. Now since the names of Ola’s routines change slightly, and the entire solution has kind of a long name, I’m going to refer to them collectively as SQLMaint. As well, I’m going to refer to the Minion suite of maintenance routines collectively as Minion.

For those of you who don’t know, Security Theater is when you have the appearance of security, but in reality you’re not secure at all. Your security measures are just for show. We see this everywhere, and it’s probably one of the main reasons that we’ve got so many data breaches happening all over the world. We’ve got admins making security decisions that they never bother testing, or never bother even questioning so while their intentions are good, they wind up with just the appearance of security. You’ll never be smarter than every hacker on the planet, but don’t get in your own way either.

So here I’m going to compare and contrast the methods that both Minion and SQLMaint use to perform their tasks and see which one is honestly more secure in your environment.

Let’s start by looking at how each product does its job.

Minion: Powershell and xp_cmdshell

First let’s look at Minion. The Minion tools use a combination of Powershell and xp_cmdshell inside SPs. Now, I’ve heard screams from some admins that Powershell isn’t secure because it could allow someone to do something inside SQL or the OS that they don’t have rights to do. This is called privilege escalation and it’s outright false. Powershell is a product like anything else. It has to follow security protocols the same as any other program. That means that there are no hooks inside Windows that notice when a Powershell script is running, and give it extra access. It simply runs under the context of the account that started the script. So Powershell is like any other scripting language from that respect. And if you’re accessing SQL Server from Powershell, then you’re still limited to your rights inside SQL itself. Powershell has no native way to access SQL so there’s no way for SQL to notice that Powershell is trying to connect and give it extra privileges. You access SQL from Powershell from one of 3 different methods: .net, sqlcmd, or invoke-sqlcmd.

Nobody has accused .net of privilege escalation, so making this call from Powershell wouldn’t do it either as you’re literally creating a .net framework object and connecting to SQL through any of those methods. And nobody thinks that sqlcmd gives you any extra rights either. You just connect to SQL and either use your AD account or your SQL account and you get the perms you’d have anyway. And of course, invoke-sqlcmd is just a Powershell wrapper for sqlcmd so there’s no extra security stuff going on in there either.

This is a good time to mention that Powershell is just a SQL client like anything else. SQL itself doesn’t even know Powershell exists. So when you connect to SQL through Powershell, as far as SQL is concerned, it might as well be SSMS, or Excel, or a website, or VBScript, or Python, etc. They’re all just clients and SQL doesn’t do anything special for any one of them. So the idea that Powershell leads to unintended privilege escalation is just that… it’s an idea. But it’s completely false. It has to follow the same security rules everything else does. And believe me, I really wish it would give me the rights to do things my personal account can’t.

Now does that mean that someone can’t fool Powershell into running under a different account so that it has more rights? Of course not. Hackers are smart. They’ve got all kinds of ways to get around things. But Powershell isn’t any less secure than VBScript, which comes on your box by default. So if they exploit a security mechanism they can use that exploit against Powershell or VBScript or even just batch file commands.

Second, the Minion tools use xp_cmdshell. By many admins this is considered to be a huge security hole. Why? Well in short the issue is definitely with privilege escalation. The issue isn’t with what they’re afraid the Minion tools are doing; they’re afraid of what someone else could do with xp_cmdshell enabled. Because with this enabled, someone could fairly easily access the OS layer and do all kinds of things under the SQL service account credentials.

SQLMaint: sqlcmd

SQLMaint works by calling sqlcmd from a job step. This is considered more secure because you don’t have to enable anything special outside of the default security configuration. However, I’m going to show you that this actually isn’t more secure, it can actually be considered less secure. So let’s get into it and I’ll show you what I mean.

xp_cmdshell is limited to sysadmins by default

Ok, we’ll start with xp_cmdshell. Let’s look at how you enable xp_cmdshell and the implications it has.

You implement xp_cmdshell by turning it on in sp_configure. This is an instance-level config so once it’s on, it’s on for all the DBs on that instance. Here’s the code you run to turn it on:

First you have to have ‘Show Advanced Options’ enabled.

Sp_configure ‘show advanced options’, 1


Sp_configure ‘xp_cmdshell’, 1


*Here’s something few people know. You actually only have to specify enough letters to make the option unique. So in the first command above you really only have to do this:

Sp_configure ‘show’, 1

If there were two options that started with ‘show’ you’d have to put enough letters in there to make it unique.


Now that it’s on, by default it’s only available to sysadmins. Nobody else can access xp_cmdshell in any way unless you give them access. And what does this mean in reality? It means that sysadmins have rights to do through xp_cmdshell what they’ve already got rights to do anyway. They’re really not getting any extra rights since most DBAs have full rights on the box anyway. And since xp_cmdshell runs under the context of the SQL service, then they’re running under whatever rights it has. This is one of the big reasons why it’s important to follow the least privilege rule for your service accounts. The other reason is because someone who knows the service account password could login under that account and do stuff and have their tracks completely covered. The threat doesn’t have to come from outside the company.

How to grant access to xp_cmdshell for non-sysadmins

You can give non-sysadmins rights to xp_cmdshell, but it takes an extra step. Since you don’t want just anyone running with full access, you have to define a proxy account. This proxy account provides the security context for all xp_cmdshell executions performed by non-sysadmins. This means that your non-sysadmins don’t automatically have unfettered access to anything on the OS or the network, because you’re going to make sure that the proxy account has only the rights it needs. You’re not going to make the proxy account an admin on any of your boxes. Here’s how you create the proxy:

EXEC sp_xp_cmdshell_proxy_account ‘Domain\ProxyLogin,’$$$$###MyStr0ngPassw0rd!@#!@#!!!’

And yes, it has to be an AD account… or at least a local Windows account (I would imagine). And the reason is simple. The only reason for running xp_cmdshell is to access OS-level things. The OS has nothing to do with SQL security so you need to pass it a Windows account. Now you can grant any non-sysadmin execute rights on xp_cmdshell.

The question is do you need to give non-sysadmins access to xp_cmdshell? I have to say that in my 20yrs in SQL, I think I can remember needing this only like once… maybe twice. The point is, that this is a lot like linked servers. The answer is almost always NO, unless there’s such a compelling reason that can’t be gotten around any other way. And that’s almost never. So in all but the strictest of circumstances, xp_cmdshell is only going to be available to your admins who have OS rights to do what they need anyway. Xp_cmdshell just makes it easier for them to code it.

The dangers of sqlcmd

Now let’s look at the method SQLMaint uses to launch its routines. Like I said, SQLMaint calls sqlcmd, which is an OS-level cmdline executable. So you have to have a way to make an OS-level call. And the way this is done is by using the command line job step type. So this job step type allows you to write any cmdline operation you need and it’ll run it for you, you guessed it, under the service account credentials. So by default this functionality is turned on and most people don’t even know it. And if you don’t know it’s there by default then how can you lock it down? The good news though is that only sysadmins have access to this type of job step by default. But anyone who has rights to alter jobs can make changes to the step.

So what does this mean for security vs security theater? Well, with xp_cmdshell you know you’re turning it on and you have to go out of your way to create the proxy and give non-sysadmins rights to it. So at every step of the way you’re aware of what’s going on. But with the job step you’re accepting the default config so anyone with the rights can come in and create a job with this job step and do something they’re not supposed to, or better yet, just alter the current job step in SQLMaint’s call.

Here’s a call I took for SQLMaint’s backup routine directly from its website:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE dbo.DatabaseBackup @Databases = ‘USER_DATABASES’, @Directory = ‘C:\Backup’, @BackupType = ‘FULL'” –b

The above command backs up the user DBs and this is the code that’s inside his command line job step. Now, what if I wanted to be malicious? I could easily open the job and alter it to do this:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “CREATE LOGIN [MyNewSALogin] with password = ‘StrongPassword’; EXEC master..sp_addsrvrolemember @loginame = N’ MyNewSALogin ‘, @rolename = N’sysadmin’; EXECUTE dbo.DatabaseBackup @Databases = ‘USER_DATABASES’, @Directory = ‘C:\Backup’, @BackupType = ‘FULL'” -b

Ok, so I just gave myself sysadmin on this instance. And I know what you’re thinking. You have to have admin rights to be able to make this change. Well, that’s not even close to accurate. Not only can you have job manager perms, but you don’t have to have any of those perms. In fact, a regular user can make these types of changes with very minimal rights. Let me give you a scenario that’s not only possible, but is highly likely.

You have a production box that has lots of jobs on it. You have different groups that need to create, alter or manage these jobs because as the DBA team you don’t want to have to get involved every time one of these many jobs has an issue, or every time they need an update… because it happens a lot. So you give that app team rights to manage jobs. Here’s where the fun begins. There are multiple ways this can go wrong, but I’ll go with the simplest. All your user needs is 3 permissions inside msdb, and here they are:

grant select on sysjobs to [SecurityTest] grant select, update on sysjobsteps to [SecurityTest]


I created a SecurityTest user and gave him access to msdb. Then I granted the perms above. Now the only thing the user has to do to recreate what I did above is run a simple update.

update sysjobsteps
set command = ‘CREATE LOGIN [MyNewSALogin] with password = ”StrongPassword”; EXEC master..sp_addsrvrolemember @loginame = N” MyNewSALogin ”, @rolename = N”sysadmin”; ‘ + command
where job_id = ‘0C06625F-F518-4D86-9E5A-063AE8B9C4E4’
and step_name = ‘BackupDBs’


He can query sysjobs to get the list of jobs and find the one he’s interested in, and then update sysjobsteps to make the change. Now, the next time that job runs, he’ll have a sysadmin account. He’s then free to cover he tracks by removing the changes, and even give himself a couple backdoors in case someone discovers his new account. This can even include adding this type of code inside of other SPs or jobs so that if his rights are ever removed, they’ll be put back. And depending on how many instances run off of the same SQL service account, he could easily spread his access to every other server in your shop. And he doesn’t even have to be that smart to do it. This isn’t all that clever of a hack.

But you see what’s happened here, right? You wanted to give him rights to manage his jobs and you ended up giving him sa. And he didn’t need that many rights to do it. See, one of the misunderstandings is that the msdb tables are system tables. They’re not. They’re regular user tables, so you can easily update them directly.

Lock down sqlcmd!

But how do you protect against this? Well, the same way you did for xp_cmdshell. You create a proxy account and run those command line job steps under an account with much lesser rights. This way, even though someone might change the code the job runs, it’ll fail because the account won’t have the rights to make the change.

Security is a complicated animal with many facets, twists, turns, and pitfalls. And to say that one solution is more secure than another just because one of them uses a feature that’s turned off by default simply isn’t the case. I hope I’ve proven that turning on xp_cmdshell isn’t inherently bad, and I really hope you understand now that simply leaving it off doesn’t mean that you’re secure. In fact, I’d say you’re at greater risk because you’re not likely to have addressed this issue in your jobs. You’re merely engaging in Security Theater. You have to be a sysadmin to turn on xp_cmdshell and you have to give someone specific rights to run it after creating a proxy. But you could innocently give someone perms in msdb and give them the keys to the kingdom.

So I hope you start looking at the xp_cmdshell issue differently because it’s a wonderful feature that allows you to do some very cool things. And it lets Minion tools give you some really awesome functionality that you wouldn’t have otherwise.

Powershell to Excel

I got this question on twitter recently so I decided to blog it real quick. The question is, how do you output data to excel from powershell?

Well, there’s a free function that I’ve used and I’ve been quite happy with it. I’m not going to give a lot of explanation here except to say that when using it on a large dataset I’ve found it to be painfully slow. And by painfully slow I mean several hours. However, using it with a small amount of data isn’t too bad. I like it because it does all the lifting for me. I just output to the function and I’m done. So anyway, here’s the link to the site. The dev does a good job of giving examples.

What’s the CPU usage on the server? Use #Powershell!!

We have here an easy, handy-dandy way to find out how much CPU is in use on your server. Of course you can find out by opening up task manager, or by running perfmon, but that’s not really the point. We want something quick and easy. We want something we could potentially use for monitoring, alerting, or tracking over time in SQL Server.

Well of course the answer is Powershell!

Open up Powershell right now, and type “Get-WmiObject win32_processor | select LoadPercentage“. That’s how much CPU is in use right now.

Now let’s say that you have a recurring intermittent CPU spike on your SQL Server. How could we use this? Let’s take a look at our script:

PSoutput NVARCHAR(2000)

SET @posh = ‘powershell “Get-WmiObject win32_processor | select LoadPercentage”‘;

( PSoutput )
EXEC xp_cmdshell @posh;

WHERE   PSoutput LIKE ‘%[0-9]%’;


Here’s what we’re doing:

  1. Create a temp table #PS to hold our output.
  2. Save the Powershell command as a variable.
  3. Run the Powershell command using xp_cmdshell (this will have to be enabled using sp_configure), and load the results into #PS.
  4. Select the only numeric value in #PS, trim the spaces off it, and cast it as an integer.

From here, we could do several things.

  • If we wanted to track CPU over time, we could stick this in a job to save that value to a table, and have it run every few minutes.
  • Or, we could set up an alert if the CPU goes over a percentage.
  • Better yet, a hybrid solution would be to track just the instances of CPU over a threshold (say, 90%), and alert if it happens several times.

We have a solution in place (on a server that spikes CPU randomly) that runs sp_WhoIsActive to a table if CPU is above threshold, and alerts if the CPU has been high for 3 samples out of the last 5. It keeps us from running pell-mell to the computer every time an alert happens, because we set it up to gather information automatically.

Of course it would also be useful to see how much CPU that SQL Server itself is using, to compare against the server total. But that’s a blog for another day.


Sean McCown

Jen McCown


This is a MidnightDBA collaborative blog.

SQL Server 2012 Install Gotcha

For those of you who like scripting your installs as much as I do there’s something you should be aware of when installing 2012 on Windows 2012 R2. I don’t remember coming across it in Windows 2012 RTM, but maybe I’m wrong. All the same though…

When you install SQL 2012 on Windows 2012 R2 you’ll get an error part way through saying that you have to install NetFx3.
Normally you’d do this in PS as part of your script like this:

Add-WindowsFeature net-framework-features

Unfortunately you’ll get an error when you try to install this by default on a fresh Windows install. Look at the pic below…


You can see that while the net-framework-features are present, the net-framework-core is ‘Removed’. This is actually what you’re looking for. So where do you get it? On the Windows DVD of course. So now your PS code to install this features looks like this… I’m assuming your Windows DVD in in the D:

Add-WindowsFeature net-framework-features -source D:\Sources\sxs

Now that leaves us with a problem. How do we get the Windows guys to install this for us by default when they give us our box? Because I’ve had very limited success getting them to change their image in a lot of places and they don’t want to go in afterward and install anything manually. So here’s what you do.
Get a hold of the Windows media just once. You can pull it from your MSDN account, or from the network share where they keep the Windows install, or just ask them for it. When you get it copy that entire folder to your SQL install folder. It’s only 275MB. Now you can have a copy of it for yourself and you don’t have to bother them about it. And being able to pass in the -source param means you can point it to any directory you like.
So now the top of your PS install script will look like this:

Add-WindowsFeature as-net-framework
Add-WindowsFeature net-framework-features -source D:\Sources\sxs

Now, if you’re doing your install through the wizard you will have to do this step. And I think I’ve heard that setup will complete just fine if you don’t install this feature, but I haven’t confirmed that personally yet. I also suspect that if it does complete manually just fine without it that it may go ahead and complete if it scripted, but it may shoot up an error in the log. And there’s just no reason to create errors when you don’t have to.

I’ll try to find time to test a couple different scenarios and let you know how it goes… you know, in all my copious free time.

Powershell: Format array elements for SQL queries

Ok, here’s the situation. You need to format an array of values for use in a SQL query. You want the final output to look something like this:

SELECT * from dbo.T1 where col1 IN (1, 15, 17, 23, 55, 67, 88)

The elements in the IN clause are what you got from the array. It doesn’t matter how you got the elements into the array, just that you did. However, for the purposes of this, we’ll just fill the array ourselves.

$a = 1, 15, 17, 23, 55, 67, 88

So now that we’ve got the elements in the array. For me this is typically the result of a query. I’m usually doing something like this:

$a = invoke-sqlcmd -query "select ID from SomeTable where col1 = 'current'"

That’s how I typically fill that array. So anyway, this method works really well as long as the values in the array are numbers. See, in the SQL statement they don’t have to be surrounded by quotes so you can just join them.
Here’s how you join array elements into a single string in PS.

$a = 1, 15, 17, 23, 55, 67, 88
$b = [string]::Join(", ", $a)
$Query = "SELECT * from dbo.T1 where col1 IN ($b)"

The JOIN method takes 2 arguments: The first is the element you want to place between each array element, in my case a comma followed by a space, and the array to apply it to.

Like I said above though this doesn’t really help when the elements need to be quoted.
What I need this time is for the SQL query to look like this:

SELECT * from dbo.T1 where col1 IN (‘2/2/2103’, ‘2/3/2013’, ‘2/4/2013’, ‘2/5/2013’)

The problem is that the elements aren’t quoted when they come in. If you wanted you could add the quotes to the query as you get it back from SQL, but there’s no guarantee you have control over that. And let’s be honest, it’s just fun to do it in PS.

Here’s how to use the same JOIN method in PS to surround the array elements in quotes.

$a = '2/2/2103', '2/3/2013', '2/4/2013', '2/5/2013'
$b = [string]::Join("', '", $a)
$Query = "SELECT * from dbo.T1 where col1 IN ('$b')"

Actually, the differences here are subtle. What’s changed is in the Join method, instead of placing a comma between the elements, I’m placing that comma in single quotes. Here’s the result what you’ll get right after you do the JOIN.

2/2/2103′, ‘2/3/2013’, ‘2/4/2013’, ‘2/5/2013

Notice it gave you the quotes everywhere except the outer boundaries. And we took care of that by placing single quotes around it in the $Query line.

Alright, that’s it. You can now surround array elements in quotes if you need to so you can format the array for use in a SQL query.
Good luck.

Log Management Made Easy

Hey guys… I wrote a nice little log management script I thought some of you might like. It doesn’t do anything to your system so it’s safe to run. Well, I’m turning xp_cmdshell on and off so if you don’t like that then this script isn’t for you.
I’m just putting more info into SQLPERF. These are the most common things I need to know when I have a runaway log. Of course feel free to modify it any way you like. Unfortunately, due to when PS came into play, you can only run this on SQL2K8 boxes and above. If you want to run it on lower boxes you’ll have to take out the PS portion of it.

One more thing before I give you the script. You’re responsible for anything you run on your box. So don’t blame me if something happens and you mess something up. Like I said, I believe this script to be safe but only if you manage the xp_cmdshell portion properly. If you run the script as it is now it’ll turn off xp_cmdshell and if you’ve got processes that rely on it then they will fail. So just be warned yet again that this is a risk. And the reason I’m turning it off again is because I don’t want to open up anything on your box that shouldn’t be. But I really like having the extra info so I don’t have to go look it up. I hope you do too.

Some important notes about the script:
1. It gives you the physical location of the log files, so if there’s more than 1 you’ll see more than 1 entry for each DB.
2. The ShrinkCmd has a variable at the top used to control its default. I like to shrink down to 1GB a lot of times, but set this to whatever you like.
3. You can expand this for yourself in ways I can’t do for you. For instance you could tie it to your log backup job to see when the last execution was and if it failed.
4. I’ve added the last log backup date for you… you’re welcome.
5. The nature of how PS works, you have to change the instance name at the top to the current instance you’re working with. There’s nothing I could do about that. I tried to make it as simple as possible.
6. The PS portion relies on xp_cmdshell. However, I turn it on and off for you in the script. If you want it left on, make sure you comment that portion out of the code or you could have stuff that breaks because you just turned on xp_cmdshell.

declare @LogSize int,
		@Instance varchar(100)
SET @LogSize = 1024
SET @Instance = 'localhost\default' -- If default instance then put Default ex: 'MyServer\default'
DBName varchar(100),
LogSizeInMB float,
LogSpaceUsedInPCT real,
Status tinyint
INSERT #LogSpace
EXEC ('dbcc sqlperf(logspace)')
DBName varchar(100),
LogBackupDate varchar(25)
exec sp_configure 'show advanced options', 1
exec sp_configure 'xp_cmdshell', 1
declare @cmd nvarchar(2000)
		SET @cmd = 'sqlps "cd sqlserver:\sql\' + @Instance + '\databases; $a = dir; foreach($DB in $a){$DBName = $DB.Name; $LogBackupDate = $DB.LastLogBackupDate; invoke-sqlcmd -query ""INSERT ##LogSpacePSTemp SELECT ''$DBName'', ''$LogBackupDate''""" -SuppressProviderContextWarning}"'
		--PRINT @cmd
		exec xp_cmdshell @cmd, no_output
, LS.LogSizeInMB
, LS.LogSpaceUsedInPCT
, D.log_reuse_wait_desc as LogReuseWait
, LT.LogBackupDate as LastLogBackup
, DATEDIFF(mm, LT.LogBackupDate, getdate()) as MinsSinceLastLogBackup
, D.recovery_model_desc as RecoveryModel
, MF.physical_name 
, 'USE [' + + ']; DBCC SHRINKFILE([' + + '], ' + CAST(@LogSize as varchar(10)) + ')' as ShrinkCmd
from #LogSpace LS
INNER JOIN master.sys.databases D
ON D.Name = LS.DBName
INNER JOIN master.sys.master_files MF
ON D.database_id = MF.database_id
ON LT.DBName = LS.DBName
drop table #LogSpace
drop table ##LogSpacePSTemp
exec sp_configure 'xp_cmdshell', 0
exec sp_configure 'show advanced options', 0
reconfigure reconfigure

What is an Overload?

When I teach powershell the question I get from DBAs most often is what is an overload. Because when we get into methods you can’t go very long at all without stating that they’re overloaded and most DBAs just don’t live in that world enough to know programming terms like that.

Overloading is when a function, method, or whatever can be called with different sets of parameters. Let’s take a good example of calling the ChangePassword() method under the Logins context. I’m gonna walk you through this step by step because I want to make sure you understand.

Let’s start by opening SSMS and right-clicking on Logins. Then to go Start Powershell.
You’ll be presented with a prompt in the Logins context. Now, if you do a get-member you’ll see a list of properties and methods. We’re concerned with the methods here. As a quick aside, a property is an adjective. It tells you something about the object, while a method is a verb. It’s something you can do to the object. So in our case you can see several properties like SID. SID is a property of a login… it describes something about the login. But the method ChangePassword() is a verb. It’s something we can do to the login. Methods are like functions in SQL more or less. Anyway…

So here’s a query you can type to get down to the info we’re interested in. I’m going to narrow the results down to just the ChangePassword() method we’re interested in so we don’t have to pick through a bunch of results.

?Download LoginsGM.txt
dir | gm | ?{$ -eq "ChangePassword"} | FL

Here’s a screenshot of the results.

Ok, now we can actually talk about the overloads. Notice that in the Definition column you there are 3 ways to call this method.

1. System.Void ChangePassword(string newPassword)
2. System.Void ChangePassword(string oldPassword, string newPassword)
3. System.Void ChangePassword(string newPassword, bool unlock, bool mustChange)

In the 1st one you can simply pass in the new password.
In the 2nd one you can pass in the old password and the new password.
and in the 3rd one you can pass in the new password along with a bit to unlock the acct and whether they must change it when they login next time.

Each one of these sets of parameter combinations is called an overload. Sometimes you’ll see a method that has no overloads. That means there’s only one way to call it. The Refresh() method is like this. It’s not overloaded because you can only call it one way… with no parameters.

dir | gm | ?{$ -eq "Refresh"} | FL


You can see from the output here that there is only one item listed in the Definition column. There’s only one way to call the Refresh() method, and that’s with no parameters. If you try to call it any other way you will get an error.

So a method can either be overloaded or not. And this is how you know how to call any method. You do a get-member on it and look at its definition. You’ve probably noticed by now that the definition also gives you the data types and functions of the parameters it’s expecting. Above for the ChangePassword() method you can see that the newPassword parameter is a string. So you automatically know you’re going to call it like this:
And you know the 3rd overload takes a string and 2 booleans. So it’ll look like this:
ChangePassword(“MyN3wPassw0rd!!!”, 1, 1)
or like this:
ChangePassword(“MyN3wPassw0rd!!!”, $true, $true)

Ok guys, that’s about it for overloads. I wanted to give a nice thorough explanation for the beginners out there. Sometimes these concepts can be a bit foggy to understand and .Net guys always explaining things with .Net terms doesn’t really help. So I wanted to try to put it in nice plain English.
Let me know if you have any questions.

Why is adopting Powershell so hard?

One of the biggest battles I fight when I teach PS or show it to different companies is adoption.  DBAs and managers just don’t get why they should be using PS to manage their environments.  And the reason so many of them are slow to adopt is because the right message is very hard to come by.  In fact, I see the wrong message all the time.  This was driven home again just now as I received my “SQL Server Pro UPDATE” email (read it here).  The editorial was about why DBAs should care about PS.  It was written by Michael Otey (pronounced Otee) and I’m sorry to say that as the Sr. Technical Director he’s completely missed the mark.  He’s managed to relegate the most important management tool DBAs have to a mere sideline player that allows you to combine SQL work with OS-level work.  Dude, you’ve completely missed the boat!!!

I’ve said this a number of times and I’ll say it again here.  Let’s get the right message out about PS.  Let’s start telling everyone that truth about it and that truth is that you’re going to get left behind if you don’t know PS and you’re not going to be able to provide true value to your company.

I’m a working production DBA in a very large shop.  And I can tell you with absolute authority that PS is more than just a way to give you some OS-level functionality in your SQL processes.  PS is vital to the management of my shop.  The primary role of PS is to allow you to manage hundreds of objects at once with just a few lines of code.  As you’ve seen in my videos again and again, and in my classes again and again, you just can’t manage any decently sized environment by hand.  It’s just too much and it’s accident-prone.

Now, a large environment doesn’t have to mean hundreds of servers.  Oh no, it can mean dozens of DBs on a single server.  It can mean dozens of schemas in a single DB, or dozens of tables, SPs, views, etc.  Or it can even mean dozens of jobs or logins.  It doesn’t matter what you’re cycling through.  The point is that PS is much more efficient at managing these objects than T-SQL will ever be.  There may be a built-in SP for running a T-SQL statement against all the DBs on your server, and there’s even one for running something on all the tables I believe.  But there’s nothing for all the jobs, or SPs, or views, or logins, or credentials, or any of the other many objects you can manage natively with PS.  With just a few characters of code you can manage all the tables in your DB.  And with just a few more characters you can do that same operation against all the DBs on your server.  And with yet just a few more characters of code, you can do that same operation against all the DBs across as many servers as you like.  THAT’S THE POWER OF POWERSHELL.  It’s more than just a way to manage files while you’re doing some T-SQL operations.

A perfect example is adding user accounts to DBs on several boxes.  We quite often get contractors who come in and do specific work for us and they need to be given rights to all of the environments for a given application.  So this one we have has 500+ DBs on each server.  There are 7 prod servers, 3 QA servers, and 5 dev boxes.  All of them have 500+ DBs on them.  So that’s 15 servers, and at least 7500 DBs that I have to add these users to.  And using windows groups will only take you so far because when the next contractor comes in he may need different rights so you still find yourself modifying the perms regardless.  The point is I go through this exercise about once every 2mos and PS has made it a trivial matter.  And that’s just ONE example.

The time has come to stop downplaying the role of Powershell in SQL Server.  DBAs need to be running to it.  We’re being asked to do more and more with less time and fewer resources.  And there are too many courses being taught, too many SQLSaturday sessions, too many videos (FREE on for you to remain in the dark any longer.  I’ve managed 900+ servers by myself using mainly PS.  I’d say it goes quite a bit deeper than being a helper to keep you from using xp_cmdshell.

Powershell till you drop

Ok, well I’ve been very busy again and released 3 new PS vids today. 

There’s one on dropping tables.  I use regex to make this happen, so even if you’re not interested in dropping tables, you can come learn how to do a simple regex.


The next one is on truncating tables.  Here I just limit it by a specific schema.

And the last one is just damn good.  I show you how to get sp_configure functionality in PS.  There’s a trick to it so don’t discard it cause you think you can figure it out on your own.