One of the commonly audited and most risky security issue is to have an id without a password. This can be avoided by turning windows security on for windows authenticated passwords but still those of us who have mixed mode authentication still have to risk having this. One of the most popular tools used to identify a blank password is the Microsoft Security Analyzer – but some environmental dependancies make this tool hard to install plus the DBAs have the additional overhead of applying patches to the tool and maintaining it as well.

So a simple way to monitor blank passwords – in our environment is by setting up a sql agent job that runs every day and writes to the event log when it fails (or in other words finds a blank password). We have an event log monitoring tool that picks up the failure and alerts us immediately. The job can be altered also to send an email or run a custom exe as the case may be. The script we use is:

1 SQL 2005/08
select name from sys.syslogins where pwdcompare('''', password) = 1

2 SQL 2000
select name  from master.dbo.syslogins where isntname = 0 and (password is null or pwdcompare(password,'''') = 1)

Would be interesting to know of more ways to accomplish this.