Retrieve user properties and Group Memberships from Profile database of User Profile Service - SharePoint 2010

SQL Queries -  

Use this query to see the one or multiple user’s properties from Profile Database.

SELECT  UserProfile.NTName,  PropList.PropertyName,  UserProfileVal.PropertyVal

 FROM [dbo].[UserProfile_Full] UserProfile WITH(NOLOCK)

JOIN  [dbo].[userProfileValue] UserProfileVal

     ON  UserProfileVal.RecordID =  UserProfile.RecordID

JOIN  [dbo].[PropertyList]     PropList

     ON  PropList.PropertyID =  UserProfileVal.PropertyID

WHERE  UserProfile.NTName IN ('domain\user1', 'domain\user2')

Use this query to find the membership of a user in various AD groups.

SELECT  UserProfile.NTName, UserProfile.PreferredName, MemberGrp.Displayname

FROM [dbo].[UserProfile_Full] UserProfile WITH(NOLOCK)

JOIN  [dbo].UserMemberships UserMembership

    ON  UserProfile.RecordID =  UserMembership.RecordID

JOIN  [dbo].Membergroup MemberGrp 

      ON UserMembership.Membergroupid = MemberGrp.id

where UserProfile.NTName like '%domain\user1%'