03 April,2019 by Tom Collins
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 $reportstyle="" $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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |