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

Ok, first the disclaimer:

Microsoft does not endorse or support the direct manipulation of the Content or Service Application databases directly (with the exception of the logging DB). Doing so would invalidate your support with Microsoft. I recommend that you query the API if you need access to this information programmatically.

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

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:

Login

Name
Email
Modified
Property

Value

Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 UserProfile_GUID 1A89D7FA-BLAH-BLAH-BLAH-1B5C0A3642DD
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 SID 0x010500000000000BLAHBLAHBLAHBLAHBLABLAHBLAHBLAHBLA
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 AccountName Domain\Admin
Domain\Admin Admin Admin@domain 2012-01-23 20:55:46 FirstName Admin

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:

Login Name Email Manager Title WorkPhone

This solution isn’t totally elegant, but neither is querying for data directly from a SharePoint database.

8 thoughts on “SharePoint 2010–SQL View to get User Profile Property Bag Values”

  1. Good to be visiting your weblog once far more, it continues to be months for me. Nicely this post that i’ve been waited for so lengthy. I want this article to total my assignment in the university, and it has very same topic together along with your post. Thanks, terrific share.

  2. Thank you for sharing. Not to many people in your position are so gracious. Your article was very poignant and understandable. It helped me to understand very clearly. Thank you for your help.

Leave a Reply