User table password field

• learn Yukon

  SSC Rookie

  Points: 35

  More actions

  November 17, 2005 at 5:38 pm

  #152627

  I want to create table name "App_Users" with follwoing fields

  1) auserid - bigint

  2) ausername - varchar(20)

  3) password --varchar(20)

  what data type should i used for password field so i can have folloing functionality

  1) sql login user can not view password by firing query

  select * from App_users

  2) my application should varify password when user enter it.

  Please guide me for designing table structure ?

  Thanks

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  November 17, 2005 at 7:28 pm

  #604554

  You could use the SQL Server password encryption routine.

  Create table App_users

  ( auserid - bigint

  , ausername varchar(20)

  , EncryptedPassword varbinary(256)

  )

  Then, during the login process with a password, encrypt it and then compare to the value in the table.

  declare @Password Nvarchar(128)

  , @EncryptedPassword varbinary(256)

  set @Password = 'AB1gS3cr3t'

  set @EncryptedPassword = CAST( pwdencrypt( @Password ) as varbinary(256))

  select @EncryptedPassword

  SQL = Scarcely Qualifies as a Language

• learn Yukon

  SSC Rookie

  Points: 35

  More actions

  November 22, 2005 at 10:31 pm

  #605388

  Very Thanks Carl Federl ,