Skip to main content

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:


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


Popular posts from this blog

Modifying Endpoint URLs on Availability Group Replicas

I recently had to modify the Endpoint URLs on our SQL Server Availability Group replicas.  The reason for this blog post is that I could not answer the following questions: Do I need to suspend data movement prior to making this change?  Would this change require a restart of the database instance? I spent enough time searching on my own to no avail that I tossed the question to the #sqlhelp hashtag on Twitter and Slack but didn't get an answer prior to executing the change request. After reading the relevant documentation, I think it's probably a good idea to suspend data movement for this change. The T-SQL is straightforward.  USE MASTER GO ALTER AVAILABILITY GROUP [AG1]  MODIFY REPLICA ON 'SQL2012-1' WITH (ENDPOINT_URL = 'TCP://'); ALTER AVAILABILITY GROUP [AG1]  MODIFY REPLICA ON 'SQL2012-2' WITH (ENDPOINT_URL = 'TCP://'); ALTER AVAILABILITY GROUP [AG2]  MODIFY REPLICA ON 'SQL2012-1

PASS Summit 2012 - Gone to the mountain and returned wiser I began my conference schedule by attending Allen White's pre-con "Automating SQL Server with PowerShell". Allen starts by telling everyone in attendance “We all can learn something from each other.  We all know something that someone else doesn't.” I thought this was a great intro and inspiration to the attendees to participate in the PASS Community. Later in the day while answering a question, Allen tells us he is not a PowerShell expert.  Which kind of surprises me.  He says he’s just figured out how to use PowerShell with SQL Server. I think he is being a bit too humble.   Afterwards, I talk to Allen about a script I’m working on and he points me in a direction that hopefully will help me finish it. All in all, it was e xcellent day of training on using PowerShell with SQL Server. As the main conference began, I tweeted about how tight the seating was in some of the rooms on the first day of the main conference.   After the Sum

PowerShell: Quick SQL Server Version Check

I have to keep track of our SQL Server version inventory.  The goal is to reduce the SQL Server 2000 population as fast as possible. The following PowerShell script will produce a csv file containing the database server name and the version of SQL Server it's running. 1: ## Get SQL Version installed on multiple servers ## 2: ## ./sqlver.ps1 3: $start = get-date 4: write-host "Start: " $start 5:   6: [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 7:   8: $FilePath = "C:\Output" 9: $OutFile = Join-Path -path $FilePath -childPath ("SQLVersions_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log") 10:   11: # Version inventory 12: @(foreach ($svr in get-content "C:\Input\AllLOBServers.txt") 13: { 14: $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr 15: $s | select Name, Version 16:   17: }) | export-csv -noType $OutFile 18:   1