PowerShell is the DBA’s Friend

In large companies I have managed 158 production servers as the primary DBA as well as shared responsibility for 400+ servers. As an example of some of the requests I get are “we don’t know which server this user has access to, but go check all 400+ server and take his access out” or another example “disable job XYZ on all production servers that you take care of”. As a last example is “for this SOC audit that is happening, give us a list of all users from all servers and all of their access”. Fun times right? I can run a query against a list of servers and output the SOC audit into Excel or disable a job on all servers and output the results to Excel with one PowerShell script in minutes. To be more specific, this script will put one sheet in Excel for each server in the list and name the sheet the server name, you can even have it save the file and close the file if you want to, just uncomment those lines close to the end of the script by removing the hashtag (#) at the beginning of the line. A hashtag is also called a pound sign.

One of the problems I had learning PowerShell is some of the basics which I will cover here. You will need to download and install the SQLPS module installed on the work station or server you run the PowerShell script from. If you have SQL Server 2012 installed then you can skip the need to import the SQLPS module. I put a copy of the command to import the SQLPS module here:

Import-Module “sqlps” -DisableNameChecking

The next basic item just to run a script is the security settings on the more recent versions of Windows. Under Windows Server 2003, these settings did not exist, but with Windows 8, Windows Server 2012 and Windows Server 2008. So if you see an error like
“scriptname.ps1 cannot be loaded because the execution of scripts is disabled on this system. For more information, see about_Execution_Policies…” then you have run into the security settings problem. The default settings requires that you get a certificate of authentisity or Cert for short. There are a couple of options, you can set up your own Cert authority on your own domain, and Microsoft provides the software to do this, and this will require a lot of overhead, which I won’t go into. The other option is to lower the security from its default to nothing like it was under Windows Server 2003. This is a bad practice and I am reluctant to give the command to do it, but I will say that the command can be found easily by searching the web using the error.

The code example I will provide is for a SOC audit and it has two separate queries to get a full list of access. The list of server names to act on is contained in a text file named “INPUT_SERVER_LIST.txt”, simply open it up and put the server names in one per line, and it can have SQL Server instance names in the format “servername\instance”. The query for $SQL1 will give who has SA and other server role information and the query for $SQL2 will give detailed access inside of individual databases. It takes a combination of both to get an accurate assessment. There is a third query type and that is what is granted to public, hopefully your company has never done that.

Notice that $SQL2 is actually a series of SQL statements, you can put just about anything into this including mass deployment of databases or mass turn a job off. A word of warning about the size of the output from the existing $SQL2, some of the returns are too large and will throw errors in PowerShell but it will finish the entire list. What I did was click through each sheet to make sure it had output and then ran the query in SSMS when it was blank.

In the body of the PowerShell script, look for the following 2 lines:

# $objRecordset.Open($SQL2,$objConnection,$adOpenStatic,$adLockOptimistic)

and you will see that the lines are almost duplicates with the only difference being $SQL1 and $SQL2 and one line is commented out with #. Take turns by commenting one out and putting the other in.


#I got the basis for this PowerShell script from “Windows PowerShell Cookbook”
#by Lee Holmes.

$serverList = “INPUT_SERVER_LIST.txt”

# constants.
# and we put the queries in here
select @@servername as [ServerName], [name], isntgroup, sysadmin, securityadmin
,serveradmin ,setupadmin, processadmin, diskadmin
from syslogins

create table #output(
[ServerName] [nvarchar](128) NULL,
[dbname] [nvarchar](128) NULL,
[Role] [sysname] NOT NULL,
[DatabaseUserName] [sysname] NOT NULL,
[UserType] [nvarchar](60) NULL,
[PermissionType] [nvarchar](128) NULL,
[PermissionState] [nvarchar](60) NULL,
[ObjectType] [nvarchar](60) NULL,
[ObjectName] [nvarchar](128) NULL
exec sp_MSforeachdb @command1=’USE [?];
insert #output
@@SERVERNAME as ServerName
,DB_NAME() as dbname
,[Role] = roleprinc.[name]
,[DatabaseUserName] = memberprinc.[name]
,[UserType] = memberprinc.[type_desc]
,[PermissionType] = perm.[permission_name]
,[PermissionState] = perm.[state_desc]
,[ObjectType] = obj.type_desc
,[ObjectName] = OBJECT_NAME(perm.major_id)
FROM sys.database_role_members as members
JOIN sys.database_principals as roleprinc
ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN sys.database_principals as memberprinc
ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN sys.database_permissions as perm
ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
select * from #output;

$excel = New-Object -Com Excel.Application #open a new instance of Excel
$excel.Visible = $True #make it visible (for debugging more than anything)
$wb = $Excel.Workbooks.Add() #create a workbook
$currentWorksheet=1 #there are three open worksheets you can fill up
foreach ($server in (Get-Content $serverList))
{ #only create the worksheet if necessary
if ($currentWorksheet-lt 4) {$ws = $wb.Worksheets.Item($currentWorksheet)}
else {$ws = $wb.Worksheets.Add()} #add if it doesn’t exist
$currentWorksheet += 1
$currentName=$server.Replace(“\”,”_”) # and name the worksheet
$ws.name=$currentName # so it appears in the tab
# note we create the query so that the user can run it to refresh it

$myconnectionstring = “Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=$server”
$adOpenStatic = 3
$adLockOptimistic = 3

$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset

$objRecordset.CursorLocation = 3

# $objRecordset.Open($SQL2,$objConnection,$adOpenStatic,$adLockOptimistic)


$rows = $objRecordset.RecordCount+1

$qt = $ws.QueryTables.Add($objRecordset,$ws.Range(“A1”))
# and execute it
if ($qt.Refresh())#if the routine works OK
[void] $Excel.Columns.AutoFit()


$filename=$filename -replace ‘[\\\/\:\.]’,’ ‘ #remove characters that can cause problems
$filename = “$filename.xlsx” #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
#$wb.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
#$wb.Close() #close the document
#$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
# Hristo Deshev’s Excel trick ‘Pro Windows Powershell’ p380



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: