SQL Server Sysadmin Audit Report Powershell script

03 April,2019 by Jack Vamvas

Question: I need a powershell script which reports on SQL Server logins with Sysadmin privileges. My aim is to automate the process - so if you could provide a script which provides the sql server name , login and that I can schedule.

 

Answer:  This powershell script will execute across a list of servers - as defined in the file associated with the $instancepath variable.Create a text file and place 1 or more sql server instances in the file e.g 

server1\instance1

server2\instance1

 

 

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath


$instancepath=$basepath + "\config\instances_all.txt"
$outputfile="\logs\sql_servers_sysadmin_report" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile

$filePath = ""


#invoke stylesheet
. .\modules\stylesheet.ps1




$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{
$instance
$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "
SELECT 
@@servername,
mem.name [ID],
rol.[name] [Role]
FROM sys.server_role_members srm
INNER JOIN sys.server_principals rol ON rol.principal_id = srm.role_principal_id
INNER JOIN sys.server_principals mem ON mem.principal_id = srm.member_principal_id
WHERE rol.[name] = 'sysadmin'
GROUP BY mem.[name],rol.[name]
 "
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
 
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle -body "SQL Servers sysadmin report"  | Set-Content $outputfilefull  



 


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


Share:

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 on SQL Server Sysadmin Audit Report Powershell script


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