PWDENCRYPT and DataType of column


I was wondering what the data type needs to be for the password column in order to use the PWDENCRYPT and PWDCOMPARE functions of sql server?

Also, wondering whether I am on the right right track her with the following syntax:

SELECT     *
FROM       Users
WHERE     (Login = 'Charlie') AND (1 = pwdcompare('mySecretPassword', Password))


Who is Participating?
Actually, the password is stored as varbinary(256) and not 255.

The passwords are stored on the master..sysxlogins table.
Simply run the following query to get the data type of the password column:

select * from syscolumns where id = object_id('sysxlogins')

You'd see that the xtype = 165 (varbinary), and length = 256.

Regarding your query- it seems fine, although you haven't really specified what it is that you are trying to do.
brdrokAuthor Commented:
currently the DataType for the column "Password" is VARCHAR(50).  Want to make sure that this ist he right column before harassing the dba to change the column type.

Based on this link:

the encrypted password will be varbinary(255)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

brdrokAuthor Commented:
thanks for your reply obahat,

SELECT     *
FROM       Users
WHERE     (Login = 'Charlie') AND (1 = pwdcompare('mySecretPassword', Password))

is suppose to return a row if the login and the password matches.  nothing terribly fancy.

i execute this statement:
select * from syscolumns where id = object_id('sysxlogins')

but only got a blank row.  Do not have Enterprise Manager nor Query Analzyer, thus, I am kinda working blind.  Maybe also because I do have restrictive access to my database.  

"You'd see that the xtype = 165 (varbinary), and length = 256"
can you please explain what"xtype" is.
Anthony PerkinsCommented:
This works for me:

Declare @Users table (
            Login nvarchar(50),
            [Password] nvarchar(128))
Insert      @Users (Login, [Password]) Values ('Charlie', PWDEncrypt('mySecretPassword'))

FROM      @Users
WHERE      Login = 'Charlie'
      AND PwdCompare('mySecretPassword', [Password]) = 1
brdrokAuthor Commented:

thanks...sorry...should have closed this question earlier.i asked the dba to change the datatype from varchar to varBinary and it works like a charm.  

Why is it so particular about data types?... I wonder...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.