Find out weak sql logins

select
b.name LoginName
, Isnull(c.name,'Public') ServerRole
, b.create_date CreateDate
, b.modify_date ModifyDate
, b.is_disabled Disabled
, case when d.is_policy_checked = 1 then 'Yes'
else 'No'
end PolicyChecked
, case when d.is_expiration_checked = 1 then 'Yes'
else 'No'
end ExpirationChecked
into #cte
from sys.server_role_members a
right join sys.server_principals b on a.member_principal_id = b.principal_id
left join sys.server_principals c on a.role_principal_id = c.principal_id
left join sys.sql_logins d on b.name = d.name




set nocount on
DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))
--Define weak password list
--Use @@Name if users password contain their name
INSERT INTO @WeakPwdList(WeakPwd)
SELECT ''
UNION SELECT '123'
UNION SELECT '1234'
UNION SELECT '12345'
UNION SELECT 'abc'
UNION SELECT 'default'
UNION SELECT 'guest'
UNION SELECT '123456'
UNION SELECT '@@Name123'
UNION SELECT '@@Name'
UNION SELECT '@@Name@@Name'
UNION SELECT 'admin'
UNION SELECT 'Administrator'
UNION SELECT 'admin123'
UNION SELECT ''
--Please add passwords you want to check if that is password for one of your sql logins.


CREATE TABLE #TEMP
(
DBName varchar(128),
UserName varchar(1280),
LoginName varchar(1000),
RoleName varchar(2000)
)
insert into #TEMP
exec sp_MSforeachdb 'with cte1
as
(
select "[?]" DBName, b.name as UserName, c.name as RoleName , suser_sname(b.sid) LoginName
from [?].dbo.sysmembers a
join [?].dbo.sysusers b
on a.memberuid = b.uid join [?].dbo.sysusers c
on a.groupuid = c.uid
)
select DISTINCT DBName, UserName,LoginName, RoleName = substring((select ( '', '' + RoleName)
from cte1 b
where a.UserName = b.UserName
FOR XML PATH ('''')
),3,8000) FROM cte1 a'

SELECT @@sERVERNAME ServerName, t1.name [Login Name], REPLACE(t2.WeakPwd,'@@Name',t1.name) As [Password], #TEMP.RoleName DBRoleName , #cte.ServerRole
FROM sys.sql_logins t1
INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1
OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1)
left join #temp on t1.name = #TEMP.LoginName
left join #cte on #cte.LoginName = #temp.LoginName
go
drop table #TEMP

go
drop table #cte