How to: Convert windows user to forms user

 

To change the authentication type of a user from windows to forms you have to run the provided sql script with two modifications applied:

  1. Change the value of @UserID variable with the UserID of the real user you want to convert. To see the UserID run the following sql script in the database:
  2. USE [TeamPulse]
    GO

    SELECT UserID FROM [User]
    WHERE Username = 'username' -- CHANGE THIS with the USERNAME of the user that is being converted
     

  3. Change the value of  @ModifyingUserName with the domain and the username of the user that you want to convert.
  4.  

Sql script:

USE [TeamPulse]
GO

DECLARE @UserID int = 0 -- CHANGE THIS to the UserID (from the User table) of the user that is being converted
DECLARE @ModifyingUserName nvarchar(128) = 'DOMAIN\username' -- CHANGE THIS to the username of the person who is executing this script
DECLARE @AppID uniqueidentifier
DECLARE @AspnetUserID uniqueidentifier
DECLARE @Username nvarchar(256)
DECLARE @Email nvarchar(256)

-- Get the membership provider application ID
SELECT TOP 1 @AppID = ApplicationID FROM aspnet_Applications

-- Get the current user's information
SELECT
 @AspnetUserID = NEWID(),
 @Username = u.Username,
 @Email = u.Email
FROM [User] u
WHERE u.UserID = @UserID

-- Add some rows to the membership provider tables for the user (Password defaults to password.  Use the app to change it)
INSERT INTO [aspnet_Users] ([ApplicationId], [LoweredUserName], [UserId], [UserName], [MobileAlias], [IsAnonymous], [LastActivityDate])
VALUES (@AppID, lower(@Username), @AspnetUserID, @Username, NULL, 0, GETDATE())

INSERT INTO [aspnet_Membership] ([UserId], [ApplicationId], [Password], [PasswordFormat], [PasswordSalt], [MobilePIN], [Email], [LoweredEmail], [PasswordQuestion], [PasswordAnswer], [IsApproved], [IsLockedOut], [CreateDate], [LastLoginDate], [LastPasswordChangedDate], [LastLockoutDate], [FailedPasswordAttemptCount], [FailedPasswordAttemptWindowStart], [FailedPasswordAnswerAttemptCount], [FailedPasswordAnswerAttemptWindowStart], [Comment])
VALUES (@AspnetUserID, @AppID, N'qf+D9+eAu63zIrh+oPmOugP4gEY=', 1, N'lEcdCghFXdqjSa4WNDu0oQ==', NULL, @Email, lower(@Email), @Username, N'yK9d4swHm4PYBL5sqpz3/Wdxt4M=', 1, 0, GETDATE(), GETDATE(), GETDATE(), '17540101 00:00:00.000', 0, '17540101 00:00:00.000', 0, '17540101 00:00:00.000', NULL)

-- Update the current values for the user
UPDATE [User]
SET
 Domain = '',
 AspnetUserID = @AspnetUserID,
 LastModifiedBy = @ModifyingUserName,
 LastModifiedDateUtc = GETUTCDATE(),
 LastModifiedSystemID = 0
WHERE
 UserID = @UserID

  
After running the script the password of the user is changed to 'password'. It is highly recommended to change it. For more information about editing user refer to this page.

 

Example

If I want to convert user with UserID = 1, my username is 'example' and my domain is 'myDomain then the script should start with(if my user is also forms @ModifyingUserName would be = 'example'):
use [TeamPulse]
GO

DECLARE @UserID int = 1
DECLARE @ModifyingUserName nvarchar(128) = 'myDomain\example'
...