Blog Detail

Hi Guys,

Today I am going to tell you all the possible solutions that you can try if you don't have admin access to Episerver Commerce account using some small SQL queries.

This is useful when you have restored the databases from any other server or you are working on a local environment setup for further development. Using below queries you can set the admin password, unlock admin user, update and approve the admin account and add the admin user to administrators role.

So for this, you need to find two values-

1. Database Name - This is the name of your commerce database.
2. Application Name - This is the name of the application that is stored in the commerce database.

Please refer my previous blog post to see how you can retrieve these values Link

If you followed my previous post then now you are almost done with requirement gathering.

So now again go back to the SQL Server Management Studio and select the commerce database.

Right-click on the database and click"New Query". The query editor will open so copy and paste the below queries in this editor and update the highlighted values that gathered in the above steps. 

These are the standard queries that you can try -

Set Commerce Admin Password

USE [EcfSqlConnection_0fc2aba3]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[aspnet_Membership_SetPassword]
		@ApplicationName = N'ECApplication',
		@UserName = N'admin',
		@NewPassword = N'password1',
		@PasswordSalt = N'salt',
		@CurrentTimeUtc = N'1/1/2019',
		@PasswordFormat = 0

SELECT	'Return Value' = @return_value

GO
                                                                                    

Unlock Commerce Admin User

USE [EcfSqlConnection_0fc2aba3]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[aspnet_Membership_UnlockUser]
		@ApplicationName = N'ECApplication',
		@UserName = N'admin'

SELECT	'Return Value' = @return_value

GO
                                                                                    

Update and Approve Commerce Admin Account Information

USE [EcfSqlConnection_0fc2aba3]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[aspnet_Membership_UpdateUser]
		@ApplicationName = N'ECApplication',
		@UserName = N'admin',
		@Email = 'no-reply@admin.com',
		@Comment = NULL,
		@IsApproved = 1,
		@LastLoginDate = '1/1/2018',
		@LastActivityDate = '1/1/2018',
		@UniqueEmail = NULL,
		@CurrentTimeUtc = NULL

SELECT	'Return Value' = @return_value

GO
                                                                                    

Add Admin Account to Administrators Role

USE [EcfSqlConnection_0fc2aba3]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[aspnet_UsersInRoles_AddUsersToRoles]
		@ApplicationName = N'ECApplication',
		@UserNames = N'admin',
		@RoleNames = N'Administrators',
		@CurrentTimeUtc = N'1/1/2019'

SELECT	'Return Value' = @return_value

GO
                                                                                    

Try to login again with your credentials

FYI - If you are not sure what is missing then fire all these queries and then try to login

Thanks for reading this blog post I hope it helps

Thanks and regards

Ravindra S. Rathore