THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

T-SQL Tuesday #016:Check Your Service Accounts with PowerShell

T-SQL Tuesday #016:Check Your Service Accounts with PowerShell

T-SQL TuesdayThis T-SQL Tuesday is about Aggregate Functions.

It may be a bit of a stretch, but a security best practice to use separate service accounts for all your SQL Server services, so I've written some PowerShell code to check to see if any account is used more than once on a given machine. I take advantage of the SQLWmiManagement DLL to find the SQL Server services, which is a safer bet than filtering on a service name.

First I load the SQLWmiManagement DLL and initialize an empty collection to hold the unique service accounts used by the various services.

# Load SMO Wmi.ManagedComputer assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

$srvaccts = @()  # Initialize a collection to hold the service accounts

Next, I've got a function which checks to see if a string value passed in as an argument already exists in the collection, returning either $True or $False.

# Create a function to iterate through the collection to check for duplicates
function Check-Duplicate([string]$cacct) {
	foreach ($sacct in $srvaccts) {
		if ($sacct.ToLower() -eq $cacct) {
			return $True
	return $False

Now we can create a new ManagedComputer object connecting to the desired machine.

$mach = '.'
# Connect to the instance using SMO
$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $mach

Next, we iterate through the Services collection of the ManagedComputer object and get the ServiceAccount property. We pass that value to the function, and if it returns $False we add it to the collection of unique accounts used.

# Iterate through the services to check accounts
foreach ($srv in $m.Services) {
	[string]$acct = $srv.ServiceAccount

	# Add the Service Account to the collection if it's not already there
	$chkdup = Check-Duplicate($acct)
	if ($chkdup -eq $False) {
		$srvaccts += $acct

Here's where it gets interesting. We can now iterate through the unique service accounts and pull just the services used by each account into a variable called $tmp. If the Count property (see - I said there was aggregation involved) is greater than 1 for a given account, it fails the security best practices test, and we return the ServiceAccount, Name and Type properties.

# For each service account used, get the services used by that account
# and if there's more than one, return the account, name and type of service
foreach ($sacct in $srvaccts) {
	$tmp = $m.Services | where {$_.ServiceAccount -eq $sacct}
	$srv = $tmp | where {$tmp.Count -gt 1} | select ServiceAccount, Name, Type

The great thing is that if there are no duplicates, there's no result. If there are results, it's something I need to look into and correct.


Published Tuesday, March 8, 2011 3:25 PM by AllenMWhite



AaronBertrand said:

Allen, inside the function you are using .ToLower() on $sacct. But I don't see anywhere that you are applying .ToLower() against the new accounts you come across. If the comparison is case-sensitive, shouldn't you also do:

 [string]$acct = $srv.ServiceAccount.ToLower()

Or at least, in the function:

 if ($sacct.ToLower() -eq $cacct.ToLower())


March 8, 2011 3:31 PM

AllenMWhite said:

Good catch, Aaron. (In an earlier version I'd done that but in cleaning it up for publication I missed that. Funny, because it works as written.)

March 8, 2011 3:40 PM

Chad Miller said:

You could simplify this by using the group-object cmdlet:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

$mach = '.'

$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $mach

$ | select name, ServiceAccount,Type | group-object -Property ServiceAccount | where {$_.Count -gt 1 }

Also SqlWmiManagement will not work against SQL 2000 only 2005 or higher. You may want to consider using gwmi win32_service

March 8, 2011 5:17 PM

AllenMWhite said:

Thanks, Chad, but the script in question will need to be readable by people just starting in PowerShell, and sometimes the "all on one line" constructs are difficult for newbies to figure out.  Also, my target for this script requested SQL 2008 and higher, so this works just fine.

March 8, 2011 6:04 PM

Chad Miller said:

I think its also important for the new PowerShell user to think in terms of PowerShell and this means thinking in terms of objects, embracing the pipe operator and using built-in cmdlets.

Once you start thinking "PowerShell" it becomes a lot easier. As a SQL Server professional PowerShell sometimes reminds me of dyslectic T-SQL. In this example we see PowerShell uses select, group-object, where and T-SQL uses select, where group by.

March 8, 2011 8:26 PM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement