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.

31 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
  8. "Customized CRM Software Solution" and "CRM Applications Development Services India For Your Business" - Hire the expert custom CRM software developers for your crucial projects. Our highly motivated professionals are ready to serve you 24*7.

    ReplyDelete
  9. SQL Server is the back end database of the Dynamics CRM web application. The complex database architecture ensures all the security features.Let’s explore the database area of Dynamics CRM and how to Use SQL and filtered views to retrieve data for reports - MS Dynamics Development Services In USA

    ReplyDelete
  10. I’ve been absent for a while, but now I remember why I used to love this web site. Thank you, I will try and check back more often. How frequently you update your web site?
    hire python developers

    ReplyDelete
  11. These provided information was really so nice,thanks for giving that post and the more skills to develop after refer that post. putlocker Your articles really impressed for me,because of all information so nice.

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

    ReplyDelete
  13. Company you should be determined about your reasons i.e. the purpose for your

    Taxi Sim 2016 Mod Apk

    ReplyDelete
  14. Thanks for your great post.We are the leading seo company in mumbai. Hire our seo agency in mumbai today for seo services in mumbai

    ReplyDelete
  15. Really nice post very informative and useful for me. We are the leading Internet Marketing Hire our SEO Company in USA

    ReplyDelete
  16. We are a search engine optimization company in Dubai, UAE. SEO Services in Dubai We offer our best SEO services to businesses looking to rank higher on Google and get the attention they deserve

    ReplyDelete
  17. I would like your content its really nice and unique content. Wooden Wall Decor Share store details, promotions, or brand content with your customers.

    ReplyDelete
  18. I stumbled upon your blog and wanted to say how much I appreciate the content you're sharing here. Your writing style is engaging, and I find your posts insightful and enjoyable to read.

    Microsoft Gold Partners In Dubai

    ReplyDelete