Auditing blank passwords in SQL Server 2005 and 2008 proves a bit more challenging than in SQL Server 2000. In SQL Server 2000 we could query syslogins and see what the password column contained. If it was NULL, then we knew that if that was a SQL Server login, we had a problem. In SQL Server 2005 and 2008, however, if we query sys.sql_logins, the password_hash column will always be populated, even if the password is blank. That means we'll need another means to audit the blank password. The trick is to attempt a password reset where we specify the old password to be blank. Here's a quick script that tests all of the SQL Server logins on a server.

DECLARE @SQL NVARCHAR(4000); 
DECLARE @login sysname; 

DECLARE cursLogins CURSOR FORWARD_ONLY 
FOR SELECT name FROM sys.sql_logins; 

OPEN cursLogins; 

FETCH NEXT FROM cursLogins INTO @login; 

WHILE @@FETCH_STATUS = 0 
BEGIN 
  PRINT 'Testing [' + @login + ']...'; 
  SET @SQL = 'ALTER LOGIN [' + @login + '] WITH PASSWORD = '''' OLD_PASSWORD = '''';'; 
  EXEC(@SQL); 
  FETCH NEXT FROM cursLogins INTO @login; 
END 

CLOSE cursLogins; 
DEALLOCATE cursLogins;  

In our results pane, if we get an error, that's a good thing. It means that the old password wasn't blank. However, if we don't get an error, we have a problem. Here is an example:

With RegularJoe we receive an error saying we can't change the password because we don't have permission. This is because the OLD_PASSWORD option was specified. I'm running with sysadmin rights, so normally I would be able to do so. However, the OLD_PASSWORD option is the trick. By setting it to blank, we can verify that none of our SQL Server based logins are blank. If they are, we won't see an error, like with the login I created called BlankPassword. No error means the password reset was successful. And that means the old password was blank. That login will need correcting.