I need to retrieve all user details including role and email addresses. I was able to query from Users, Usersinrole, role table from core db. However, I couldn't find the table to query the email addresses. Our users are synced from AD. I checked the membership table which has the email column but the table only contains manually created sitecore users. Are users synced from AD saved in sitecore db? Which table is it saved?
dbo.aspnet_Membership table in Core database is where user details such as Email address, password etc are stored.
By default, automatically created Sitecore users (who are created in the beginning) won't be having any email address associated to them. You can verify by opening User Manager from the Sitecore Launchpad. The email column will be blank by default for automatically created users such as sitecore\admin.
So, that is why you are only seeing email addresses for users who are created manually in the Email column of the