SharePoint 2010–SQL View to get User Profile Property Bag Values

Ok, first the disclaimer:

Now for the good stuff. There may be cases where you need to expose or review the User Profile properties and for whatever reason the API just isn’t a good solution. For example, you are interfacing with a legacy system that does not understand Web Services but you want to expose a current list of all SharePoint users and their associated properties for systems like an external phonebook or a Roles-Based access system. But let’s say that the system is only able to interface with something like SQL views or CSV files (yuk!). Here is a solution that should be somewhat future-proof (at least until the next version of Office/SharePoint 15 comes around).

1. Create an empty Database on your SharePoint SQL Server. Per my standards, I always call it “WSS_Content_Views”.

a. I HIGHLY recommend that you never-ever create a view, table, or stored procedure inside an actual live SharePoint Content database as these can and do get overwritten/dropped/die when you apply a patch, cumulative update, or service pack.

2. Run the following script:

CREATE VIEW [dbo].[vw_UserProfilePropertyBag]
AS
SELECT     upf.NTName AS Login, upf.PreferredName AS Name, upf.Email, upf.LastUserUpdate AS Modified, pl.PropertyName AS Property, upv.PropertyVal AS Value
FROM         SharePoint_User_ProfileDB.dbo.UserProfile_Full AS upf INNER JOIN
                      SharePoint_User_ProfileDB.dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
                      SharePoint_User_ProfileDB.dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID

<

p style=”margin-top: 12px; margin-bottom: 12px;” align=”left”>

EDIT: In some cases, the PropertyVal will be a GUID identifier, and the human readable values we want are stored in SecondaryVal, so in order to get both values you’ll want to create a view that looks like this instead:

CREATE VIEW [dbo].[vw_UserProfilePropertyBag]
AS
SELECT     upf.NTName AS Login, upf.PreferredName AS Name, upf.Email, upf.LastUserUpdate AS Modified, pl.PropertyName AS Property, upv.PropertyVal AS Value, 
                      upv.SecondaryVal AS SecondaryValue
FROM         SharePoint_User_ProfileDB.dbo.UserProfile_Full AS upf INNER JOIN
                      SharePoint_User_ProfileDB.dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
                      SharePoint_User_ProfileDB.dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID

This is assuming that SharePoint_User_ProfileDB is where your user profile information is stored. Out of the box it usually looks something like this: “User Profile Service Application_ProfileDB_c09fc7e8a1c444d9a9822583c7545ab3”; However spaces and GUIDS in databases always to bug me so I usually change the DB name during the setup.

3. Now you can query against the view, using the following:

SELECT * FROM [WSS_Content_Views].[dbo].[vw_UserProfilePropertyBag]

4. Your result set will contain the following, listing out each property pairing that you have set up in the User Profile, including custom properties:

5. From this master view of the User Profile Property Bag, you can then create further customized views to roll-up the information that the external application might need into one row per Login, specifying only the specific fields that you want to expose to the external application. For example, if you wanted a basic phonebook view:

CREATE VIEW [dbo].[vw_BasicExternalPhonebook]
AS
SELECT     dbo.vw_UserProfilePropertyBag.Login, dbo.vw_UserProfilePropertyBag.Name, dbo.vw_UserProfilePropertyBag.Email,
                      dbo.vw_UserProfilePropertyBag.Value AS Manager, vw_UserProfilePropertyBag_1.Value AS Title, vw_UserProfilePropertyBag_2.Value AS WorkPhone
FROM         dbo.vw_UserProfilePropertyBag LEFT OUTER JOIN
                      dbo.vw_UserProfilePropertyBag AS vw_UserProfilePropertyBag_2 ON dbo.vw_UserProfilePropertyBag.Login = vw_UserProfilePropertyBag_2.Login LEFT OUTER JOIN
                      dbo.vw_UserProfilePropertyBag AS vw_UserProfilePropertyBag_1 ON dbo.vw_UserProfilePropertyBag.Login = vw_UserProfilePropertyBag_1.Login
WHERE     (dbo.vw_UserProfilePropertyBag.Property = N'Manager') AND (vw_UserProfilePropertyBag_1.Property = N'Title') AND
                      (vw_UserProfilePropertyBag_2.Property = N'WorkPhone')

Will create a view with one line for each Login that contains the following fields:

<

p style=”margin-top: 12px; margin-bottom: 12px;”>This solution isn’t totally elegant, but neither is querying for data directly from a SharePoint database.