You can Encrypt a user password using Scalar function in SQL Server.
lets see
Execute the below code in SSMS( Below function return type is varchar )
USE [DB_NAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <MAHESH>
-- Create date: <04-11-2013>
-- Description: <Password Encryption using SHA Algorithm>
-- =============================================
Create FUNCTION [dbo].[PSWD_ENCRYPT]
(
-- Add the parameters for the function here
@pwd varchar(50)
)
returns varchar(50)
AS
BEGIN
-- Declare the return variable here
declare @Result varchar(50);
declare @hexbin varbinary(max)
set @hexbin = HASHBYTES('SHA1',HashBytes('SHA1', @pwd));
set @Result = (select cast('' as xml).value('xs:hexBinary(sql:variable("@hexbin") )', 'varchar(max)'));
-- Return the result of the function
RETURN @Result
END
Usage
Insertion
declare @pswd varchar(50)
set @pswd='mahesh';
insert into dbo.users(uid,pswd) values ('mahesh',dbo.PSWD_ENCRYPT(@pswd))
Select:
declare @pswd varchar(50)
set @pswd='mahesh';
select uid from dbo.users where uid=@uid and pswd=dbo.PSWD_ENCRYPT(@pswd)
Update :
update dbo.users set pswd=dbo.PSWD_ENCRYPT(@pswd) where .....