List all NT group logins with Powershell

10 December,2014 by Jack Vamvas

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.

SQL query to list SQL Server logins classified as NT groups is :

 

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

Read More on using Powershell and Excel

Powershell , Excel charts and data presentation - SQL Server DBA

Powershell – adding multiple worksheets to Excel ... - SQL Server DBA

Export-CSV Powershell - SQL Server DBA

 

 

 

 


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 List all NT group logins with Powershell


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