Run the SQL Script to Create and Register a User with DNN

The following SQL Script was written by Mitchel Sellers and can be found in the article Creating a Standard DotNetNuke User Via SQL.
Go to Host > SQL

Copy the following script and paste it in the text box. Alternatively you can save the file that comes with this article and load it via the Browse button. The SQL script is the following:

/*
THE FOLLOWING STORED PROCEDURE CAN BE USED TO CREATE AND REGISTER A USER WITH DNN. IN ORDER TO WORK, THIS PROCEDURE NEEDS TO HAVE A DEFAULT USER ACCOUNT FROM WHICH TO COPY THE PASSWORD DETAILS.
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE spr_dnm_RegisterUser

–@RootUser nvarchar ( 256 ),
@FirstName nvarchar (256),
@LastName nvarchar (256),
@DisplayName nvarchar (256),
@UserName nvarchar(256) ,
@Email nvarchar(256)

/*
@Password nvarchar(128), –From the existing user
@PasswordSalt nvarchar(128), –From the existing user
@PasswordFormat int, –From the existing user
@PortalId int, –From the existing data
*/

/*
@ApplicationName nvarchar(256) = ‘DotNetNuke’,
@PasswordQuestion nvarchar(256) = ”,
@PasswordAnswer nvarchar(128) = ”,
@IsApproved bit = 1,
@CurrentTimeUtc datetime = NULL,
@CreateDate datetime = NULL,
@UniqueEmail int = 0
*/

AS
BEGIN

DECLARE @RootUser nvarchar (15) = ‘dummy’

DECLARE @ApplicationName nvarchar(256) = ‘DotNetNuke’
DECLARE @PasswordQuestion nvarchar(256) = ”
DECLARE @PasswordAnswer nvarchar(128) = ”
DECLARE @IsApproved bit = 1
DECLARE @CurrentTimeUtc datetime = NULL
DECLARE @CreateDate datetime = NULL
DECLARE @UniqueEmail int = 0

/*
SET @ApplicationName = ‘DotNetNuke’
SET @PasswordQuestion = ”
SET @PasswordAnswer = ”
SET @IsApproved = 1
SET @CurrentTimeUtc = NULL
SET @CreateDate = NULL
SET @UniqueEmail = 0
*/

SET NOCOUNT ON;

DECLARE @UserId uniqueidentifier
DECLARE @DNNUserId int

DECLARE @Password nvarchar(128) –From the existing user
DECLARE @PasswordSalt nvarchar(128) –From the existing user
DECLARE @PasswordFormat nvarchar(256) –From the existing user
DECLARE @PortalId int — From the existing data

IF ( @CurrentTimeUtc IS NULL ) SET @CurrentTimeUtc = GETDATE()
IF ( @CreateDate IS NULL ) SET @CreateDate = GETDATE()

SELECT @Password = m.password, @PasswordSalt = m.passwordsalt, @PasswordFormat = m.passwordformat
FROM aspnet_users u INNER JOIN aspnet_membership m ON (u.userid = m.userid)
WHERE u.UserName = @RootUser

SELECT @PortalId = PortalID–,PortalName
FROM Portals

–Make the stored procedure call
EXEC dbo.aspnet_Membership_CreateUser @ApplicationName, @Username, @Password,
@PasswordSalt, @email, @passwordquestion, @PasswordAnswer,
@IsApproved, @CurrentTimeUtc, @CreateDate, @UniqueEmail,
@PasswordFormat, @UserId

–Insert the record into the DotNetNuke users table
INSERT INTO users ( Username, FirstName, LastName, IsSuperUser, Email, DisplayName, UpdatePassword )
VALUES ( @Username, @FirstName, @LastName, 0, @Email, @DisplayName, 1 )

–Get the new userid, from the DNN users table
SELECT @dnnuserid = userid
FROM Users WHERE username = @Username

–Now, insert the record into the user portals table
INSERT INTO UserPortals (userId, PortalId, CreatedDate)
VALUES(@dnnuserid, @PortalId, GETDATE())

–Now Give the user permissions to the RECISTERED Users group
INSERT INTO UserRoles (userId, roleId)
SELECT @dnnuserid, roleId
FROM Roles
WHERE RoleName = ‘Registered Users’

END
GO

Change the value of @RootUser variable to the default user name created for example User Name: Dummy Password: DefPassword

Check the Run as Script checkbox and press Execute

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: