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'
)