Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
MS CRM 4 SQL Agent Jobs need to run as a MS CRM user? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    It seems MS CRM 4  SQL Jobs need to run as a defined MS CRM user!

    The only way that i can get a SQL query running under a scheduled SQL 2005 job is if i tell it to run as a MSCRM user who is also able to be an administrator that can send database email. it won't read the MSCRM filtered views as "NT Authority\SYSTEM"

    So at present i have an admin user account tied up for running SQL jobs.

    Is there a smarter way to have the SQL agent having read permissionss to views?

    (I tried adding the select permission to the MSCRM database but this made no difference.)

    Cheers

    Stuart

    Stuart
    Tuesday, June 29, 2010 10:21 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Anything that queries the Filtered views needs to run as a CRM user (as the filtered views only output data that the account has CRM permissions for). There are 2 mian workarounds:

    1. Continue to run the job as e.g. SYSTEM, but use SQL impersonation (the EXECUTE AS command) to run in the SQL context of a CRM user
    2. Change the queries to use the underlying SQL views (e.g. Contact, instead of FilteredContact). There are some drawbacks to this; it's officially unsupported, you need to filter out recently deleted records (only return records where DeletionStateCode = 0), and you need to handle any datetime conversion to the local timezone
    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    • Marked as answer by Jim Glass Jr Tuesday, June 29, 2010 5:08 PM
    Tuesday, June 29, 2010 11:55 AM
    Moderator

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    Anything that queries the Filtered views needs to run as a CRM user (as the filtered views only output data that the account has CRM permissions for). There are 2 mian workarounds:

    1. Continue to run the job as e.g. SYSTEM, but use SQL impersonation (the EXECUTE AS command) to run in the SQL context of a CRM user
    2. Change the queries to use the underlying SQL views (e.g. Contact, instead of FilteredContact). There are some drawbacks to this; it's officially unsupported, you need to filter out recently deleted records (only return records where DeletionStateCode = 0), and you need to handle any datetime conversion to the local timezone
    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    • Marked as answer by Jim Glass Jr Tuesday, June 29, 2010 5:08 PM
    Tuesday, June 29, 2010 11:55 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    David,

    Thnaks for two great work around's, i'd complety forgot about EXECUTE AS, this sounds like the preferred programitical approach. maybe i can build a bit of dynamic SQL to query the user table for available admins.

    Much appreciated,

    Stuart.

    Stuart
    Tuesday, June 29, 2010 12:50 PM