SELECT Permission Denied When Consuming User Profile Service Application

**Updated to include the officially supported method**

While setting up a repro scenario a month or so ago I encountered an interesting issue with consuming the User Profile Service Application (UPSA). And by interesting, of course I mean maddening…

It appears that the documentation around how to consume a shared User Profile Service Application is not quite complete. The scenario is that I have a SharePoint Server 2010 farm consuming the UPSA from a SharePoint Server 2013 services farm. As you are aware there are six services that we can share (publish) so that other farms can consume them and make use of there information.

With the UPSA, there is one step that is slightly different from the other shared service applications and that is the following note:

Now once you add the identity of the content web application from the 2010 farm where you want to use the UPSA, you might expect all of this to “just work”… however it doesn’t. In fact, when you browse to your 2010 content web application you may be greeted with the all too familiar:

At this point we take a look in the ULS logs and find the following:

04/01/2014 17:01:40.31 w3wp.exe (0x29B4) 0x47E8 SharePoint Server Database 880i High System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'Versions', database 'aProfile', schema 'dbo'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Office.Server.Data.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock) 705298ec-2b50-4c69-b8bd-0037eaa0a688

04/01/2014 17:01:40.31 w3wp.exe (0x29B4) 0x47E8 SharePoint Server Database 880j High SqlError: 'The SELECT permission was denied on the object 'Versions', database 'aProfile', schema 'dbo'.' Source: '.Net SqlClient Data Provider' Number: 229 State: 5 Class: 14 Procedure: '' LineNumber: 1 Server: 'sql1\sp2013,57590' 705298ec-2b50-4c69-b8bd-0037eaa0a688

So there seem to be many ways to workaround this issue, but I cannot find anything documented as the “official” way. I am continuing investigation to see if we can get official guidance on this, but for now you should be able to get by this with whatever method works for you:

**Update: the only officially supported method**

• Directly grant the SELECT permission on the Versions table to the SPDataAccess role

**Unsupported alternate methods**

• Grant your web application ID inside of your User Profile database the server role db_datareader
• Directly Grant the SELECT permission on the Versions table for your web application ID
• <there may be other ways, but these are all I’ve researched at the moment>

Note that I’m showing you how to do this through SQL Management Studio. You can accomplish the same through T-SQL, but I’ll leave that to someone else to figure out.

HowTo directly grant the SELECT permission to the SPDataAccess role

1. Open SQL Management Studio and connect to the SQL instance that is hosting your SP2013 UPSA Profile database.
2. Expand the User Profile database, expand Security, expand Roles, expand Database Roles, right-click SPDataAccess, and click Properties.
3. Click Securables in the “Select a page” pane in the upper left of the dialog window.
4. Click Search on the left side and click OK (leaving the default radio button set to “Specific objects…”).
5. Click Object Types, click to select Tables, click OK, type Versions, and click OK.
6. Click to select the entry for “[dbo].[Versions]” and click OK.
7. You should now see the Versions table listed in the Securables list. Click to select it and click to select the Grant column of the Select row in the “Permissions for dbo.Versions” list at the bottom.
8. Click OK to save the changes.

HowTo add your web application ID to db_datareader

1. Open SQL Management Studio and connect to the SQL instance that is hosting your SP2013 UPSA Profile database.
2. Expand the User Profile database, expand Security, expand Users, right-click your 2010 web application Id, and click Properties
3. Click Membership in the “Select a page” pane in the upper left of the dialog window
4. Click to select db_datareader in the “Database role membership” pane and click OK.

You may need to execute an IISReset on your 2010 web front end, but at this point your web application should load and your User Profile information (including MySite configuration) should be getting serviced from the 2013 UPSA.

HowTo directly grant the SELECT permission to your web application ID

1. Open SQL Management Studio and connect to the SQL instance that is hosting your SP2013 UPSA Profile database.
2. Expand the User Profile database, expand Tables, right-click dbo.Versions, and click Properties
3. Click Permissions in the “Select a page” pane in the upper left of the dialog window
4. Click Search on the left side and either browse to locate or type in the desired web application ID and click OK.
5. Now that your web application ID is listed, be sure it is highlighted (selected) and in the lower pane select the “Grant” column of the “Select” row and click OK.

You may need to execute an IISReset on your 2010 web front end, but at this point your web application should load and your User Profile information (including MySite configuration) should be getting serviced from the 2013 UPSA.

You may need to execute an IISReset on your 2010 web front end, but at this point your web application should load and your User Profile information (including MySite configuration) should be getting serviced from the 2013 UPSA.

Comments

• Anonymous
  June 23, 2014
  We were seeing this issue in our environment and found that we had to grant dbowner permissions before the sync worked.
• Anonymous
  June 23, 2014
  Hi Konnie,Not sure why you would have needed that much permission, but if it works for you and your security team is ok with it, then run with it..   However the permissions of the consuming web application pool identity should have no bearing on the User Profile Synchronization.