Find SQL Server logins that have blank passwords

On 29 Sep, 2015

MS SQL DBA

By : Charith Silva

No Comments

Today our data security officer asked me to get list all SQL Server logins that have blank passwords and SQL Server logins that have password exactly same as username. Below script can be used to retrieve those weak SQL Logins.

Blank Passwords

SELECT name as [User Name],type_desc as [Login Type],is_disabled,default_database_name,create_date,modify_date

FROM sys.sql_logins

WHERE PWDCOMPARE('',password_hash)=1;

SELECT name as [User Name],type_desc as [Login Type],is_disabled,default_database_name,create_date,modify_date

FROM sys.sql_logins

WHERE PWDCOMPARE('',password_hash)=1;

Passwords that are the same as the Login name

SELECT name as [User Name],type_desc as [Login Type],is_disabled,default_database_name,create_date,modify_date

FROM sys.sql_logins

WHERE PWDCOMPARE(name,password_hash)=1;

SELECT name as [User Name],type_desc as [Login Type],is_disabled,default_database_name,create_date,modify_date

FROM sys.sql_logins

WHERE PWDCOMPARE(name,password_hash)=1;

Previous Post Next Post

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva