the SELECT permission was denied on the object 'FilteredActivityPointer'

While testing out some user profiles in a development environment, I was presented the following error when trying to run the Activity report, which is included with Microsoft CRM:

the SELECT permission was denied on the object 'FilteredActivityPointer'

I had been able to run the report while logged in as the administrator user that was used during the install, but no other users could, regardless of their security role. Digging into the problem, I opened SQL Server Management Studio and took a look at the permissions on the FilteredActivityPointer view:




This sure didn’t look right. So, I compared this to some other filtered views, and found that the other filtered views had permissions for the ReportingGroup and the CRMReaderRole from SQL Reporting Services.

To restore the privileges to this view, I ran the following script against the CRM database:

GRANT SELECT ON dbo.FilteredActivityPointer
TO [DYNAMICS\ReportingGroup
{0f55c689-67f1-4fba-93d5-231e488a960e}]
GO

I did the same for the CRMReaderRole:

GRANT SELECT ON dbo.FilteredActivityPointer
TO [CRMReaderRole]
GO

These simple scripts add the user specified to the view, and set the checkbox for the Select permission.

Here’s the result, which, upon testing, allowed users to run the Activity Report:

I suspect that this is due to a bug in the organization deployment process. This organization was not the default organization – I created a new Org for this project. Somehow, I think permissions did not get properly propagated to all the views.

New Article on the Microsoft CRM Team Blog

I have a new article on the Microsoft CRM Team Blog today with some tips on building a virtual development server for CRM using Windows Server 2008 and SQL 2008. Check it out!

Error Importing Customizations

On a development environment where I had imported a new organization, the developer was getting an error when trying to import customizations from another system (both on Update Rollup 3), and was unable to publish any customizations either. On importing, he got the following error:
“Failure: customeraddress: A SQL Server error occurred. Try this action again. If the problem continues, check the Microsoft Dynamics CRM Community for solutions or contact your organization's Microsoft Dynamics CRM Administrator. Finally, you can contact Microsoft Support.”

In the event log on the server was an error that said:

EventID: 19457
Source: MSCRMWebService
Customization Import failed. Error: Could not find stored procedure 'p_PublishMetadata'.

In digging into the database in SQL Server Management Studio, I saw that this sproc and another called 'p_PublishLabelsByObjectId' were listed not as dbo owned like the rest of the stored procedures, but user owned (DOMAIN\Username.p_PublishMetadata, for example).

I changed the ownership using the following query and publishing and importing started to work:

sp_changeobjectowner 'p_PublishLabelsByObjectId', 'dbo'

and then the same for the other stored procedure:

sp_changeobjectowner 'p_PublishMetadata', 'dbo'

(You'll get a caution about changing ownership that you can ignore.) Close SQL Management Studio and re-open it to verify that the ownership has changed (refreshing didn't work for me for some reason).

Somehow, when this organization was imported, the ownership didn't get set correctly on these two stored procedures. Tip of the hat to Luke Rogers for the tip on changing ownership of stored procedures one at a time.

Environment: SQL Server 2008 Standard, Microsoft CRM 4.0 UR3 Professional with the original organization disabled, and a new organization imported.

How To Uninstall SQL Server 2008 Reporting Services

I was setting up a CRM development server with SQL 2008 and made the quick, uninformed, and ultimately time-consuming decision to install Reporting Services in SharePoint integrated mode. I was also going to install WSS 3.0, so I thought I'd try this out.

Unfortunately, I found out later that CRM 4.0 does not support reporting services in SharePoint integrated mode. So, if I had been thinking clearly, this would have been a not too difficult problem: just uninstall reporting services and reinstall it in the right mode.

My problem: I couldn't figure out for the life of me how to uninstall Reporting Services for SQL 2008. I tried everything I could think of to remove it. It wasn't listed separately under the Programs and Features list, and I couldn't find any other way despite hours (ugh!) of searching.

I finally figured out that if I went to the Add/Remove Programs area (Programs and Features in Server 2008), and started an uninstall of SQL Server 2008, I would get the option to select which feature of SQL I wanted to remove. Duh. I feel like an idiot.