0

I'm using .net Membership for user management, I I'd like to make a page for admin that lists all users details.

The Membership system created tables in my sql database server and I'm trying to get all info by query but somehow the query returns nothing.

these are the relevant tables created in the server:

Membership

I tried this query: (ms sql 2008)

select u.UserName, m.Email, m.CreateDate, m.LastLoginDate, r.RoleName from dbo.aspnet_Users u, dbo.aspnet_Membership m, dbo.aspnet_UsersInRoles uir, aspnet_Roles r where (u.UserId=m.UserId) and (m.UserId=uir.UserId) and (uir.RoleId=r.RoleId)

but it returns nothing, but when I try:

select u.UserName, r.RoleName from dbo.aspnet_Users u, dbo.aspnet_UsersInRoles uir, aspnet_Roles r where (u.UserId=uir.UserId) and (uir.RoleId=r.RoleId)

It returns a table with the user name and role. I guess the problem is with the Membership table userID field but I don't know how to explain it really.

Dvirski
  • 321
  • 4
  • 16
  • 36

3 Answers3

0

Try this:

where (u.UserId=uir.UserId) and (m.UserId=u.UserId) and (uir.RoleId=r.RoleId)
Samurai
  • 3,724
  • 5
  • 27
  • 39
0

You may try this: 

SELECT u.UserName, r.RoleName FROM dbo.aspnet_Users u
INNER JOIN dbo.aspnet_UsersInRoles uir
ON u.UserId=uir.UserId
INNER JOIN  aspnet_Roles r 
ON uir.RoleId=r.RoleId
Novice
  • 558
  • 2
  • 9
  • 21
0

Replacing each INNER JOIN with a LEFT JOIN will get you what you want:

SELECT u.UserName, r.RoleName FROM dbo.aspnet_Users u
LEFT JOIN dbo.aspnet_UsersInRoles uir
ON u.UserId=uir.UserId
LEFT JOIN  aspnet_Roles r 
ON uir.RoleId=r.RoleId
Tessa
  • 23
  • 4