Joseph Michael Pesch
VP Programming

ASP.Net Security Create New User Account Based on Existing User Account

by 1. February 2013 07:39

This SQL script will create a new ASP.Net user account using an existing user account as the basis.  The new user account will have the same password and roles as the existing user account.  Also included at the end of the main script is a script to change a user account password to a known defualt (e.g. Password1234).


Script to replicate user:

declare 
  @ExisitingUserName varchar(50)
, @ExisitingUserEmail
, @OldUserID uniqueidentifier
, @UserID uniqueidentifier
, @Email varchar(50)
, @UserName varchar(50)
, @DeleteIfExists bit

select
  @UserID = newid()
, @Email = 'new.user@email.com'
, @UserName = 'new.user@email.com'
, @DeleteIfExists = 0
, @ExisitingUserName = 'EXISTING_USER_NAME_HERE'
select
  @UserID
, @Email
, @UserName

select @OldUserID = UserID 
from dbo.aspnet_Users 
where UserName = @UserName

if @DeleteIfExists = 1 begin

delete from dbo.aspnet_UsersInRoles
where UserID = @OldUserID

delete from dbo.aspnet_Membership
where UserID = @OldUserID

delete from dbo.aspnet_Users 
where UserName = @UserName

end

insert into dbo.aspnet_Users
select
  ApplicationId
, @UserID
, @UserName
, lower(@UserName) /* LoweredUserName */
, null /* MobileAlias */
, 0 /* IsAnonymous */ 
, getdate() /* LastActivityDate */
from dbo.aspnet_Users
where UserName = @ExisitingUserName

insert into dbo.aspnet_Membership
select
  ApplicationId
, @UserID
, Password
, PasswordFormat
, PasswordSalt
, MobilePIN
, @Email
, @Email
, PasswordQuestion
, PasswordAnswer
, IsApproved
, IsLockedOut
, getdate() /* CreateDate */
, getdate() /* LastLoginDate */
, getdate() /* LastPasswordChangedDate */
, '1/1/1754 12:00:00 AM' /* LastLockoutDate */
, 0 /* FailedPasswordAttemptCount */
, '1/1/1754 12:00:00 AM' /* FailedPasswordAttemptWindowStart */
, 0 /* FailedPasswordAnswerAttemptCount */
, '1/1/1754 12:00:00 AM' /* FailedPasswordAnswerAttemptWindowStart */
, Comment
from aspnet_Membership
where Email = @ExisitingUserEmail

insert into dbo.aspnet_UsersInRoles
select @UserID, RoleID
from dbo.aspnet_Roles

Script to change password:

update dbo.aspnet_Membership 
set PasswordSalt = 'dArJQD4e4upwMyqCSl9iNA=='
, Password = '1mAUj77CySFAtqlfInt4UFOznAc=' /* Password1234 */
where UserId = 
(
  select UserId 
  from dbo.aspnet_Users 
  where UserName = 'USER_NAME_HERE'
)

Tags:

ASP.Net | SQL Server

Comments are closed