Brilliant topic, has resolved some migration issues for me, however I also need the password field. Anyone got any ideas? Thanks Glynn

me again! I know, I suck. Sorry. But, heres my issue. I'm running a report to find all users created on a certain date, and with security role 'Y'. However, the report keeps returning users who do not have that role, and when I run the report to show me users who don't have y, it shows me people who do have the role. In this case, I want to find people who are no t yet 'Opt In Network Members'. Of the people who joined in the date range given, the report shows people who DO have that role, when the query below asks to NOT have that role.

SELECT DISTINCT u.LastName [Last Name], u.FirstName [First], P1.State, P2.Region, P3.Telephone, P4.Cell, u.Email, up.createddate
FROM Users U
INNER JOIN UserPortals UP on U.UserId = up.UserId
LEFT JOIN  dbo.UserRoles AS ur ON u.UserID = ur.UserID
 LEFT JOIN Roles R on UR.Roleid = R.Roleid
 LEFT  JOIN (SELECT UserID, PropertyValue AS State FROM UserProfile WHERE PropertyDefinitionID = 56) P1
     ON U.UserID = P1.UserID
 LEFT  JOIN (SELECT UserID, PropertyValue AS Region     FROM UserProfile WHERE PropertyDefinitionID = 9) P2
     ON U.UserID = p2.USERID
 LEFT  JOIN (SELECT UserID, PropertyValue AS Telephone     FROM UserProfile WHERE PropertyDefinitionID = 31) P3
     ON U.UserID = p3.USERID
 LEFT  JOIN (SELECT UserID, PropertyValue AS Cell     FROM UserProfile WHERE PropertyDefinitionID = 32) P4
     ON U.UserID = p4.USERID
WHERE up.PortalID = 0 AND R.Rolename <> 'Opt In Network Members'

You are a champion. Thank you once again Seb, I owe you a beer!