USE [database]
GO
ALTER TABLE [dbo].[t_user]
DROP COLUMN [password]
GO
ALTER TABLE [dbo].[t_user]
ADD [password] varbinary(20) NULL
GO
UPDATE t_user
SET password = HashBytes(‘SHA1’, cast(‘PASSWORDSTRING’ as nvarchar(16)))
GO
SELECT count(1)
FROM t_user
WHERE password = HashBytes(‘SHA1’, cast(‘PASSWORDSTRING’ as nvarchar(16)))
GO
ALTER PROCEDURE p_UserValidate(@username varchar(75), @password nvarchar(16))
AS
BEGIN
SET NOCOUNT ON;
–Returns userID if user/password combo IS valid
–Returns empty recordset if user/password combo IS NOT valid
SELECT A.userID
FROM t_user A WITH (NOLOCK)
WHERE upper(A.username) = upper(@username)
AND A.password = HashBytes(‘SHA1’, @password)
END
GO
ALTER PROCEDURE p_PasswordChange(@userID numeric(18,0), @password nvarchar(16))
AS
BEGIN
SET NOCOUNT ON;
UPDATE t_user
SET password = HashBytes(‘SHA1’, @password)
WHERE userID = @userID
END
GO
More Info: http://msdn.microsoft.com/en-us/library/ms174415.aspx