Solved

Admin User Changing Another User's Password

Posted on 2009-06-29
4
249 Views
Last Modified: 2012-05-07
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
Comment
Question by:Glen_D
  • 3
4 Comments
 

Author Comment

by:Glen_D
ID: 24736856
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
 

Author Comment

by:Glen_D
ID: 24736876
Don't need help with the auto email...thx
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24737197
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
 

Author Closing Comment

by:Glen_D
ID: 31597940
Thx So Much...really appreciate your help...
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now