Stored Procedure for Changing the Password

Raj.Trivedi
Posted by Raj.Trivedi under Sql Server category on | Points: 40 | Views : 8379
Hello Team,

This snippet will help you to change the password of the user who has logged into application and wants to update his password.

In this we will pass Userid,current password,new password as parameters to stored procedure.

If the current password matches the password in the database then only the new password will be updated.
If the user enters wrong current password then the new password will not be created.

Stored Proc.

ALTER procedure [dbo].[ChngPwd]
(
@User_ID uniqueidentifier,
@Currpwd varchar(15),
@Newpwd varchar(15)
)
as
begin
declare @@Currpwd4db varchar(15) -- checking the current password already in database table
declare @@rescount tinyint

set @@Currpwd4db = (select User_Password from UserMaster where User_ID = @User_ID and User_Status =1)
if (@@Currpwd4db = @Currpwd) --checking of the current password entered by user and password in database matches or not.If yes then updates the new password submitted by user
begin
update UserMaster set User_Password = @Newpwd, Modified_by = @User_ID, Modification_date = getdate()
where User_ID = @User_ID and User_Status =1
set @@rescount = 1
end
else
begin
set @@rescount = 0
end
select @@rescount as rescount
end

Comments or Responses

Login to post response