Find NULL Passwords with PowerShell/T-SQL

DBAs: Here's a small script that will find SQL Logins with NULL passwords on multiple servers.

1:  foreach ($svr in get-content "C:\Input\ProdInstances.txt" | where {$_ -notmatch "^#"})

2:  {    

3:      $svr

4:      $ExFile = 'C:\Audit\NULL_SQL_Passwords_' + $svr.Replace('\','_') + '.csv'

5:        $con = "server=$svr;database=master;Integrated Security=sspi" 

6:        $cmd = "SELECT @@SERVERNAME AS Server, name, loginname, dbname, password, accdate FROM master..syslogins WHERE password IS NULL AND isntgroup = 0 AND isntname = 0 AND loginname NOT LIKE '##%'"

7:        $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

8:        $dt = new-object System.Data.DataTable

9:        trap {"Oops! $_"; continue } $da.fill($dt) | out-null

10:        if ($dt.Rows.Count -gt 0) { $dt | SELECT  Server, Name, Loginname, DBname, Password, Accdate | export-csv -noTypeInformation  $ExFile }

11:  }

Put a list of servers in a text file like so:

Server1
Server2
#Server3
Server4
...

I called mine ProdInstances.txt and put it in the folder C:\Input.

If you need to skip a server in the list, put a # at the beginning of that line and the where clause in line 1 will cause that line to be skipped.  This is helpful when you are testing.

Line 1:  ForEach loop begins and reads the file C:\Input\ProdInstances.txt to get the list of servers.

Line 2:  Opening ForEach brace

Line 3:  Displays contents of $svr variable to console.  I use this as a progress indicator.

Line 4:  Setup csv output file to contain results.

Line 5:  Setup connection to the database server.

Line 6:  Set SQL command to be executed.

SELECT @@SERVERNAME AS Server, name, loginname, dbname, password, accdate 

FROM master..syslogins 

WHERE password IS NULL 

AND isntgroup = 0 

AND isntname = 0 AND loginname NOT LIKE '##%' 

Line 7:  Setup SQLDataAdapter.

Line 8:  Setup DataTable to hold results of SQL query

Line 9:  Execute the query and load the DataTable.  Trap statement checks for errors.

Line 10: If the result set contains any rows, write the result set to the csv file.

Line 11: Closing ForEach brace