2018-04-18

Create admin user in episerver after creating new site

If you ever create a new Episerver site with a default membership provider and cannot login since you don't have any current user account in the membership database, use the SQL script below to create a user (tempadmin/tempadmin) that belongs to the WebEditors and WebAdmin groups.

Disclaimer:
The created user will have it's password in clear text in the database so after creating this user, be sure to create a real admin user and delete this one.


/**
  Will create an application user (epiadmin/epiadmin) and group (webeditors) for an asp.net identity database

**/
DECLARE @aid uniqueidentifier
DECLARE @applicationName nvarchar(235)
DECLARE @uid uniqueidentifier
DECLARE @rolename nvarchar(256)

set @applicationName=N'/'
DECLARE @username nvarchar(50)
DECLARE @password nvarchar(50)
SET @username=N'tempadmin'
SET @password=N'tempadmin'

--create application
IF( NOT EXISTS (SELECT * FROM applications where ApplicationName=@applicationName))
BEGIN
 SET @aid=NEWID()
 insert into applications values(@aid,@applicationName,null)
END

SELECT @aid=ApplicationId FROM applications where ApplicationName=@applicationName
select * from applications

--create user
IF(NOT EXISTS (SELECT * FROM users where ApplicationId=@aid and UserName = @username))
BEGIN
set @uid=NEWID()
 INSERT INTO Users VALUES(@uid,@aid,@username,0,GETDATE())
END
SELECT @uid=UserId FROM Users where ApplicationId=@aid and UserName=@username
select * from users

--create membership data
IF(NOT EXISTS (SELECT * FROM memberships where ApplicationId=@aid and UserId= @uid))
BEGIN
 INSERT INTO Memberships(UserId,ApplicationId,Password,PasswordFormat,PasswordSalt,Email,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowsStart,Comment) 
                  VALUES(@uid,@aid,@password,0,'Tc7EIFB+4dw33bRNOVJTGQ==','tempadmin@example.com',NULL,NULL,1,0,GETDATE(),GETDATE(),GETDATE(),'1754-01-01 00:00:00.000',0,'1754-01-01 00:00:00.000',0,'1754-01-01 00:00:00.000',NULL)
END
select * from memberships

SET @rolename='WebEditors'
DECLARE @roleid uniqueidentifier

IF(NOT EXISTS (SELECT * from roles where applicationId=@aid and RoleName=@rolename))
BEGIN
 SET @roleid=NEWID()
 INSERT INTO Roles(roleid,applicationid,rolename) 
 values(@roleid,@aid,@rolename)
END
SELECT @roleid=RoleId FROM Roles where ApplicationId=@aid and RoleName=@rolename
select * from roles

IF(NOT EXISTS(SELECT * FROM usersinroles where roleid=@roleid AND userid=@uid))
BEGIN
 INSERT INTO usersinroles(userid,roleid) values(@uid,@roleid)
END
select * from usersinroles

SET @rolename='WebAdmins'
IF(NOT EXISTS (SELECT * from roles where applicationId=@aid and RoleName=@rolename))
BEGIN
 SET @roleid=NEWID()
 INSERT INTO Roles(roleid,applicationid,rolename) 
 values(@roleid,@aid,@rolename)
END
SELECT @roleid=RoleId FROM Roles where ApplicationId=@aid and RoleName=@rolename
select * from roles

IF(NOT EXISTS(SELECT * FROM usersinroles where roleid=@roleid AND userid=@uid))
BEGIN
 INSERT INTO usersinroles(userid,roleid) values(@uid,@roleid)
END
select * from usersinroles