Accessing Dynamics CRM filtered views using a SQL Server account

When writing reports or dashboards for Dynamics CRM, the filtered views are very useful. They automatically filter the data based on the application defined security without having to go through the web services.  Typically, the filtered views are the only database objects exposed to the average user in Dynamics CRM, and they are the only access method aside from web services that is officially supported. They do have a major limitation though in that only a Windows account can normally be used to access them. Using a SQL server account will result in no data being returned. There are some instances where access to the database is required, but a Windows account just isn't feasible.

There is a way to access filtered views using a SQL Server user account by taking advantage of a feature built into the filtered views. There are two steps required.

First, add the SQL Server user account, giving it access to the organization database in the CRMReaderRole role.

USE [master]
GO
CREATE LOGIN [MyUser] WITH PASSWORD=N'MyPassword'
GO
USE [ORGDB_MSCRM]
GO
CREATE USER [MyUser] FOR LOGIN [MyUser]
GO
EXEC sp_addrolemember N'CRMReaderRole', N'MyUser'
GO

Next, obtain the GUID for the Dynamics CRM user you would like to impersonate.  This can be done by querying the SystemUser table directly, or through the Dynamics CRM user interface by displaying the user's properties and examining the page URL .  Use the Transact-SQL SET CONTEXT_INFO to set the context information to this GUID before querying the filtered view.

DECLARE  @uid uniqueidentifier

SET @uid = convert(uniqueidentifier, 'user_guid_goes_here')

SET CONTEXT_INFO @uid

SELECT TOP 1000 *

  FROM [ORGDB_MSCRM].[dbo].[FilteredContact]

The query will return the records as if you were connected using the given user's Windows account.  It should be noted that the SET CONTEXT_INFO is only required when establishing the connection, and once set will remain so for the duration of the session.