• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

Admin User Changing Another User's Password

I have this sp that allows an admin to change a user's password but not sure how to validate that the admin user has a 'Role' of 6 (admin) thus allowing the user to make the change.  I also need to send an auto email to the user who had his or her pw changed with the new password...Thx


CREATE PROCEDURE [dbo].[usp_AdminChangeUserPassword]
 
 @administratorUserID int,
 @userID int,
 @newPwd varchar(20),
 @RetStatus tinyint = NULL OUT

AS

BEGIN;

      UPDATE u
      SET u.User_Password = @newPwd, u.Date_PW_Change = getdate()
      from tbl_User u join tbl_Registration r on u.UserID = r.UserID
      WHERE (@UserID = u.UserID)
      and @administratorUserID =


      SET @RetStatus = CASE WHEN @@ROWCOUNT>0 THEN 1 ELSE 0 END;

END;
RETURN;
0
Glen_D
Asked:
Glen_D
  • 3
1 Solution
 
Glen_DAuthor Commented:
Anyone...I updated this sp by declaring a variable...thx

CREATE PROCEDURE [dbo].[usp_AdminChangeUserPassword]
 
 @administratorUserID int,
 @userID int,
 @newPwd varchar(20),
 @RetStatus tinyint = NULL OUT

AS

Set @administratorUserID = (select u.userid from tbl_User u
join tbl_Registration r on u.userid = r.UserID where r.[Role] = 6)

BEGIN;

      UPDATE u
      SET u.User_Password = @newPwd, u.Date_PW_Change = getdate()
      from tbl_User u join tbl_Registration r on u.UserID = r.UserID
      WHERE (@UserID = u.UserID)
      and @administratorUserID =


      SET @RetStatus = CASE WHEN @@ROWCOUNT>0 THEN 1 ELSE 0 END;

END;
RETURN;
0
 
Glen_DAuthor Commented:
Don't need help with the auto email...thx
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE PROCEDURE [dbo].[usp_AdminChangeUserPassword]
 
 @administratorUserID int,
 @userID int,
 @newPwd varchar(20),
 @RetStatus tinyint = 0 OUT

AS

IF EXISTS (select 1 from tbl_User u join tbl_Registration r on u.userid = r.UserID where r.[Role] = 6 and u.userid = @administratorUserID)
  UPDATE u
  SET u.User_Password = @newPwd, u.Date_PW_Change = getdate()
  from tbl_User u join tbl_Registration r on u.UserID = r.UserID
  WHERE (@UserID = u.UserID)

SET @RetStatus = CASE WHEN @@ROWCOUNT>0 THEN 1 ELSE 0 END;
RETURN;

0
 
Glen_DAuthor Commented:
Thx So Much...really appreciate your help...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now