Solved

Admin User Changing Another User's Password

Posted on 2009-06-29
4
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
In this article I will describe the Copy Database Wizard 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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

624 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