sys.sql_logins.

Question

sys.sql_logins.

b_boy

SSCertifiable

Points: 5145
More actions
February 8, 2008 at 5:59 pm

#186362

Hello
I am trying to find out what users are using their login username as their passwords with the following script:
SELECT name pwdcompare (name,password_hash) as same sys.sql_logins.
For some reason whatsoever it doesn't work, can anyone explain to me why it doest?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Vivien Xing SSChampion Points: 12297 More actions · Answer 1

pwdcompare: takes two arguments - a varchar value that is a cleartext password and a varbin value that is a SQL Server password hash, and returns 1 if they match and 0 if they don't.

I used a cursor to check. Try the code below. Hope it works for you.

------------------------------------------

SET NOCOUNT ON

DECLARE @loginname nvarchar(50)

DECLARE mycursor CURSOR FOR

select name from syslogins where password is not null

OPEN mycursor

FETCH NEXT FROM mycursor

INTO @loginname

WHILE @@FETCH_STATUS = 0

BEGIN

select name from sys.sql_logins where pwdcompare ( @loginname , password_hash ) = 1

if @@rowcount = 1

PRINT @loginname + ': password is the login name'

FETCH NEXT FROM mycursor INTO @loginname

End

CLOSE mycursor

DEALLOCATE mycursor

b_boy SSCertifiable Points: 5145 More actions · Answer 2

Hello

I got the result i wanted quite alright, but the result appeared with three different grids, in which it will be hard for me to use a report.

Any suggestions?

Vivien Xing SSChampion Points: 12297 More actions · Answer 3

check the message not the result set, or custmize your own report.

b_boy SSCertifiable Points: 5145 More actions · Answer 4

Thanks

Figured it out and used the following syntax:

select Name, pwdcompare(name, password_hash) as Same_As_Login_Name from sys.sql_logins;

🙂