Wednesday, February 16, 2011

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.

19 comments:

  1. good post, I have spent a whole afternoon to find a solution.

    Thanks a lot for sharing

    ReplyDelete
  2. Thanks a lot for this. This really helped me a lot

    ReplyDelete
  3. thanks for this blog
    but is it possible to connect with the sql server's user that created with reporting services

    thanks for reply

    ReplyDelete
  4. Thank God! Finally the solution is in front of me..

    Microsoft CRM Training

    ReplyDelete
  5. Hello,

    Is this way supported by Microsoft? Does this by any chance come under the *unsupported* ways of developing XRM?

    please update.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Essential Post explaining why I have to set CONTEXT_INFO to get data out of CRM from SQL account. Many Thanks

    ReplyDelete