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
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:
This solution isn’t totally elegant, but neither is querying for data directly from a SharePoint database.