DDL Trigger to prevent creation of SQL Login with Blank Password

There was a query in MSDN forum regarding preventing creation of login with Blank password using T-SQL script. I was clueless about this. Then Rich was referred to Laurentiu Cristofor's blog. There was no direct answer but it give me a wonderful solution which I was not aware. I would like to thank both of them here. Please check the blog entry Here

So I thought to tweak pwdcompare() function and make a DDL Trigger. Here we go…

Create TRIGGER Svr_DDLtrg_Audit_BLANK_PASSWORD
ON All Server
FOR
Create_Login,Alter_Login
AS
SET NOCOUNT ON

BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

declare @loginname varchar(100)
select @loginname=@Eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)')
print @loginname

if exists (select 1 from sys.sql_logins where pwdcompare('', password_hash) = 1 and name=@loginname)
begin
Rollback
end
else Print 'good password'

End

Note : I rollback the statement if the password is blank. IF you want to audit or log then you may modify the script accordingly