Webspinner Tech Blog

My boss was hounding me about why he had to have multiple usernames for our different DNN portals (we have one for Customer Support, one for Sales and one for Intranet purposes). By default DNN didn't allow me to create users for those portals with identical user names. So after playing with the data a bit I figured out a way and wanted to share it with you and keep it here in case I need to do it again and forget how.

First we need to understand the fundamental underpinnings of the Users structure inside the database. We will be using the Host SQL tab to perform data manipulation so make sure you have the database backed up before attempting these hacks.

So User permissions are basically set in three tables:

1. Users
2. UserPortals
3. UserRoles

These are all important tables to keep consistent. So lets say you have user 'foo' in the Users table, his record would be something like this:

UserID	Username	FirstName	LastName	IsSuperUser	AffiliateId	Email	DisplayName	UpdatePassword
1	foo	Foo	Bar			foo@bar.com	Foo Bar

You could get access to this record to see it in this state by passing the SQL query:

SELECT * FROM Users WHERE Username = 'foo'

So now lets say that you have another portal where foo also needs access and you have set him up another user for that portal called foo2:

UserID	Username	FirstName	LastName	IsSuperUser	AffiliateId	Email	DisplayName	UpdatePassword
1	foo	Foo	Bar			foo@bar.com	Foo Bar
2	foo2	Foo	Bar			foo@bar.com	Foo Bar

As you can see these are essentially identical users but DNN will not let you log into portal 1 with user 2 or into portal 2 with user 1. The secret here is UserPortals. First determine what user is assigned to which portal. To do this will use the SQL statement:

SELECT * FROM Users INNER JOIN UserPortals ON Users.UserID = UserPortals.UserID WHERE Email = 'foo@bar.com'

Now notice that since the usernames are not the same, the ticket was to use a field where they match and since email is the usual suspect I decided to use that. The results of that query would look something like this:

UserID	Username	FirstName	LastName	IsSuperUser	AffiliateId	Email	DisplayName	UpdatePassword	UserId1	PortalId	UserPortalId	CreatedDate	Authorised
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	3	4	11/11/2009 9:54:51 PM
6	foo2	Foo	Bar			foo@bar.com	Foo Bar		6	4	5	11/11/2009 9:56:05 PM

Ok so now we know which portals are for which user, next lets also get the roles, because the roles are different for each portal. So we add to the above SQL statement:

SELECT * FROM Users INNER JOIN UserPortals ON Users.UserID = UserPortals.UserID INNER JOIN UserRoles ON Users.UserID = UserRoles.UserID WHERE Email = 'foo@bar.com'

Which yields us:

UserID	Username	FirstName	LastName	IsSuperUser	AffiliateId	Email	DisplayName	UpdatePassword	UserId1	PortalId	UserPortalId	CreatedDate	Authorised	UserRoleID	UserID2	RoleID	ExpiryDate	IsTrialUsed	EffectiveDate
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	3	4	11/11/2009 9:54:51 PM		9	5	7
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	3	4	11/11/2009 9:54:51 PM		10	5	8
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	3	4	11/11/2009 9:54:51 PM		11	5	6
6	foo2	Foo	Bar			foo@bar.com	Foo Bar		6	4	5	11/11/2009 9:56:05 PM		12	6	10
6	foo2	Foo	Bar			foo@bar.com	Foo Bar		6	4	5	11/11/2009 9:56:05 PM		13	6	11
6	foo2	Foo	Bar			foo@bar.com	Foo Bar		6	4	5	11/11/2009 9:56:05 PM		14	6	9

Ok so there is a lot going on now. Clearly these users are both administrators of their respective sites, now we just need to trim it down to one user, we will use UserID 5 since that is 'foo'. The SQL statement for this would be:

UPDATE UserPortals SET UserID = 5 WHERE UserID = 6;
UPDATE UserRoles Set UserID = 5 WHERE UserID = 6;
DELETE FROM Users WHERE UserID = 6;
SELECT * FROM Users INNER JOIN UserPortals ON Users.UserID = UserPortals.UserID INNER JOIN UserRoles ON Users.UserID = UserRoles.UserID WHERE Email = 'foo@bar.com'

This yields us the following result:

UserID	Username	FirstName	LastName	IsSuperUser	AffiliateId	Email	DisplayName	UpdatePassword	UserId1	PortalId	UserPortalId	CreatedDate	Authorised	UserRoleID	UserID2	RoleID	ExpiryDate	IsTrialUsed	EffectiveDate
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	3	4	11/11/2009 9:54:51 PM		9	5	7
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	4	5	11/11/2009 9:56:05 PM		9	5	7
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	3	4	11/11/2009 9:54:51 PM		10	5	8
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	4	5	11/11/2009 9:56:05 PM		10	5	8
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	3	4	11/11/2009 9:54:51 PM		11	5	6
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	4	5	11/11/2009 9:56:05 PM		11	5	6
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	3	4	11/11/2009 9:54:51 PM		12	5	10
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	4	5	11/11/2009 9:56:05 PM		12	5	10
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	3	4	11/11/2009 9:54:51 PM		13	5	11
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	4	5	11/11/2009 9:56:05 PM		13	5	11
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	3	4	11/11/2009 9:54:51 PM		14	5	9
5	foo	Foo	Bar			foo@bar.com	Foo Bar		5	4	5	11/11/2009 9:56:05 PM		14	5	9

as you can see, user foo2 has been deleted entirely and if you log into either site with foo you will have the permissions of foo and foo2 from before the hack.

Hope this helps some people out there make sense of their DNN user issues.