SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Powershell sql server security audit

20 July,2011 by Jack Vamvas

Often there is a requirement to enforce SQL Server Security Policies. This powershell script - in part 7 -
will take a list of sql server instances, iterate through every instance , generate a list of Logins and return TRUE or FALSE for the fixed server roles:
SYSADMIN,SECURITY,SERVER ADMIN,SETUP ADMIN,PROCESS ADMIN,DISK ADMIN,DBCREATOR,BULK ADMIN.

The powershell script will store the resultset into a Excel spreadsheet, and save the file . This is a useful tool for Audits , and typically I would run this script , and then chase sql server owners to clear up.

Let's break down the script


Part 1

defines the variables and importantly sets the names of : a)the instance list text file and output file
the assumption is that all relevant files exist in the same path as the powershell scripts
The instance.txt file is of the following format , include the column headers and as many instances as required:

monitor,server,Instance,torp,errorlog,ping
Y,MYSERVER1,MYSQLSERVER_1,Prod,E:\Microsoft SQL Server\MSSQL$MYSQLSERVER_1\log\ERRORLOG,Ping
Y,MYSERVER2,MYSQLSERVER_2,Test,E:\Microsoft SQL Server\MSSQL$MYSQLSERVER_2\log\ERRORLOG,Ping


Part 2
Creates the the new Excel object using COM

Part 3
Imports the csv

Part 4
Loop through every server in instance.txt


Part 5
Instantiate SMO
Create an SMO connection to the instance
The $logins = $s.Logins represents a collection of Login objects. Each Login object represents a login defined on the instance of SQL Server.

Part 6
iterate through the collection
A TRUE or FALSE will return for every  fixed server role requested. An then placed in the Excel spreadsheet


Part 7
Clean up Excel objects

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
$instancepath=$basepath + "\instances.txt"
$outputfile="\sql_server_health_security_check_" + $isodate + ".xls"
$outputfilefull = $basepath + $outputfile
#Part 2 Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$workbook = $Excel.Workbooks.Add()
$worksheet4 = $workbook.Worksheets.Add() 
$Sheet = $Excel.Worksheets.Item(1)
#$workbook = $excel.Workbooks.Open($basepath + $outputfile)
#Counter variable for rows
$intRow = 2
#Read thru the contents of the SQL_Servers.txt file
#Part 3 
$servers = Import-Csv $instancepath
##Part4#######################################################
foreach ($entry in $servers)
{
$torp = $entry.TorP
$mon = $entry.monitor
$machine = $entry.server
$errorlog = $entry.errorlog
$os = $entry.os2000
$iname = $entry.Instance

if ($iname -eq "Null")
{
$instance = "$machine"
}
else
{
$instance = "$machine\$iname"
}
if ($torp -eq "Prod")
{
$ServerType = "Production"
}
ElseIf ($torp -eq "Test")
{
$ServerType = "Test"
}
ElseIf ($torp -eq "Dev")
{
$ServerType = "Dev"
}
else 
{
$ServerType = "Unknown"
}

$instance = $instance.toupper()
#########################################################
#Create column headers
$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
$Sheet.Cells.Item($intRow,2) = $instance
################################################
#Part 5 - This script gets SQL Server database information using PowerShell
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# Create an SMO connection to the instance
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$logins = $s.Logins

$intRow++
$Sheet.Cells.Item($intRow,1) = "LOGIN"
$Sheet.Cells.Item($intRow,2) = "SYS"
$Sheet.Cells.Item($intRow,3) = "SECURITY"
$Sheet.Cells.Item($intRow,4) = "IS SERVER"
$Sheet.Cells.Item($intRow,5) = "SETUP"
$Sheet.Cells.Item($intRow,6) = "PROCESS"
$Sheet.Cells.Item($intRow,7) = "DISK"
$Sheet.Cells.Item($intRow,8) = "DBCREATOR"
$Sheet.Cells.Item($intRow,9) = "BULADMIN"
$Sheet.Cells.Item($intRow,10) = "LOGIN_TYPE"
#Format the column headers
for ($col = 1; $col –le 10; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
$intRow++
##part 6 - iterate through the collection 
foreach ($login in $logins)
{
#use name variable for remaining script
$name = $login.name
$loginType=$login.logintype
if ($loginType -eq 0)
{
$loginType = "Windows User"
}elseif ($loginType -eq 1)
{
$loginType = "Windows Group"
}elseif ($loginType -eq 2)
{
$loginType = "SQL Login"
}
$Sheet.Cells.Item($intRow, 1) = $name 
$Sheet.Cells.Item($intRow, 2) = $s.Logins["$name"].IsMember("sysadmin")
$Sheet.Cells.Item($intRow, 3) = $s.Logins["$name"].IsMember("securityadmin")
$Sheet.Cells.Item($intRow, 4) = $s.Logins["$name"].IsMember("serveradmin")
$Sheet.Cells.Item($intRow, 5) = $s.Logins["$name"].IsMember("setupadmin")
$Sheet.Cells.Item($intRow, 6) = $s.Logins["$name"].IsMember("processdmin")
$Sheet.Cells.Item($intRow, 7) = $s.Logins["$name"].IsMember("diskadmin")
$Sheet.Cells.Item($intRow, 8) = $s.Logins["$name"].IsMember("dbcreator")
$Sheet.Cells.Item($intRow, 9) = $s.Logins["$name"].IsMember("bulkadmin")
$Sheet.Cells.Item($intRow, 10) = $loginType
$intRow ++
#}
}
$intRow ++
}
$Sheet.UsedRange.EntireColumn.AutoFit()
cls
#Part 7 
$EXcel.ActiveWorkbook.SaveAs("$outputfilefull")
$Excel.Quit()
$WorkBook = $Null
$WorkSheet = $Null
$Excel = $Null
[GC]::Collect()

 Read More

How to create a SQL Server Security Audit - SQL Server DBA

Powershell - run script on all sql servers - SQL Server DBA

SQL Server - Powershell and Failed Logon attempts - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer