[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Admin User Changing Another User's Password

Posted on 2009-06-29
4
Medium Priority
?
297 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 2000 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

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.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month7 days, 14 hours left to enroll

607 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