Glen_D
asked on
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_AdminChangeUser
@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;
CREATE PROCEDURE [dbo].[usp_AdminChangeUser
@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;
ASKER
Don't need help with the auto email...thx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thx So Much...really appreciate your help...
ASKER
CREATE PROCEDURE [dbo].[usp_AdminChangeUser
@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;