PWDENCRYPT and DataType of column

Posted on 2005-04-19
Last Modified: 2008-01-09

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))


Question by:brdrok
    LVL 7

    Author Comment

    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.

    LVL 28

    Expert Comment

    Based on this link:

    the encrypted password will be varbinary(255)
    LVL 5

    Accepted Solution

    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.
    LVL 7

    Author Comment

    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.
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    This works for me:

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

    SELECT  *
    FROM      @Users
    WHERE      Login = 'Charlie'
          AND PwdCompare('mySecretPassword', [Password]) = 1
    LVL 7

    Author Comment


    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.  


    Expert Comment

    Why is it so particular about data types?... I wonder...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now