Is there any way in SharePoint 2010 to get the Distinct values of a property across all users? For example, I want a list of all Departments in the Profile Store.

In SQL I can do

SELECT DISTINCT(PropertyVal) FROM [ProfileDB].[dbo].[UserProfileValue]
WHERE PropertyID = 14

but accessing the SQL Database is a big no-no of course, so I wonder if there is something in the object model?

Note that accessing the User Information List on an SPSite is not good enough, as only a fraction of all users is in that list. I want to get it directly from the Profile Store.

I do not want to loop through UserProfileManager because a) it's sloooow and b) requires Elevated Permissions that even SPSecurity.RunWithElevatedPermissions doesn't provide.

Before I write a timer job that does that, is there some other way to do that?

  • Accessing the database isn't necessarily not allowed. It is unsupported to make changes to the database, and the tables are not documented because they can (and do) change at any time. The API is preferred for access as it is usually backwards compatible, so if you develop code that references it, it is less likely to break when an update comes out. There is nothing wrong with what you've done, provided you don't go around telling everyone that the only way to get a list of departments is "SELECT ..."
    – shufler
    Commented Dec 19, 2011 at 22:39

2 Answers 2


As far as I am aware this can only be done with choice fields. For something like the department you would likely have to iterate through the profiles.


Ended up querying Active Directory directly since the SharePoint 2010 User Profile Store doesn't have a way to be queried efficiently.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.