Solved

Admin User Changing Another User's Password

Posted on 2009-06-29
4
258 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

896 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

11 Experts available now in Live!

Get 1:1 Help Now