10 December,2014 by Tom Collins
During a SQL Server Security Review , I needed to list SQL Server Logins classified as NT groups across Production SQL Servers. It’s easy to complete manually if there are only a few servers, but if you’re dealing with hundreds of SQL Server instances , Powershell is a good option to iterate through a list of SQL Servers , gather the information and compile a HTML report.
select @@servername ,convert(nvarchar(100),name) from sys.syslogins where isntgroup = 1
You’ll notice I’ve issued a CONVERT function on sysname. The sys.syslogins view returns name as a sysname datatype. Powershell doesn’t handle the automatically the sysname datatype. Normally I recommend allowing the client application to manage the conversion, but I find it simpler with Powershell to issue the CONVERT function as part of the SQL Statement.
Wrapping this sql statement into a Powershell process is straightforward. This Powershell code iterates through a list of SQL Server instances with a valid login and executes the sql statement on the sql server. The recordsets are stored in a System.Data.Datatable. When the iteration completes the System.Data.Datatable is dumped out using the ConvertTo-HTML function , generating the HTML file.
$isodate=Get-Date -format s $isodate=$isodate -replace(":","") $basepath=(Get-Location -PSProvider FileSystem).ProviderPath $outputfile="\nt_groups_" + $isodate + ".html" $outputfilefull = $basepath + $outputfile $style="" $dt = new-object "System.Data.DataTable" foreach ($svr in get-content "C:\powershell_scripts\instances_prod.txt"){ $svr $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi" $cn.Open() $sql = $cn.CreateCommand() $sql.CommandText = "select @@servername ,convert(nvarchar(100),name) from sys.syslogins where isntgroup = 1 " $rdr = $sql.ExecuteReader() $dt.Load($rdr) $cn.Close() } $dt| select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $style -body "SQL Server NT groups (Production)" | Set-Content $outputfilefull
Feel free to use the code and customise for your own purposes. If you’d like some customisations, contact me
Powershell , Excel charts and data presentation - SQL Server DBA
Powershell – adding multiple worksheets to Excel ... - SQL Server DBA
Export-CSV Powershell - SQL Server DBA
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: |