Password Encryption in SQL 2000

/*
Written By :- Veer ji Wangoo (India) http://vsql.blogspot.com
Inputs from:- Dinesh Asanka(Sri Lanka) SqlServer Central
http://www.geocities.com/dineshasanka/index.htm
Dated :- 12 July 2004

A very simple and unique way to encrypt Password in your database application
using a function called pwdencrypt

Lets have look at it,we first create a table named [UserTab]
which will have two columns along with a UserTab .Note the data Type
varbinary(255) to include Hashed encrypted value in the Table
*/

CREATE TABLE [dbo].[UserTab] (
[ID] [int] IDENTITY (1, 1)
NOT NULL ,
[UserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varbinary] (255) NULL ,
) ON [PRIMARY]
GO


/*
Next insert the data with a usage pwdencrypt() --This is an Undocumented Function.
*/
INSERT INTO [UserTab](UserName,Password)
VALUES ( 'Vsql',pwdencrypt('Varsha'));

--When you select you will find the PWD in Encryted Form.

select * from [user]

--A Sp can be written with below logic and to retreive an d check the Password authentication
--We can ask the user to input teh USername and PWD into the Sp and check it with the matching Pwd
--If compare value comes as true then we give them access else we reject the access to th application

DECLARE @varPassword varbinary(255)
SELECT @varPassword = [Password] FROM [UserTab] where UserName = 'Vsql'
DECLARE @chkPassword varchar(255)
SELECT @chkPassword = 'Varsha'
if (pwdcompare(@chkPassword, @varPassword, 0) = 1)
Begin
print 'Get along'
End
else
print 'Permission Denied'

/*
Although this doesnt garuntee the Safegaurd of your passwords and
any hacking attacks but will certainly help in maintaining the integrity of the
applications.Like freaking with Usernames/passwords of any user by the people who has
access to tables in your Application Database

You can also use various Encryption algorithms availble in registry
and Devlopment Environments.
*/