how to set "Audit Table Report" credentials correctly? RRS feed

  • Question

  • Hello,

    I wrote an sql script which selects from the audit table, transforms the changeddate and makes it clear to view. The SQL Script works perfectly. I put the statement in visual studio report builder and designed it.

    My Problem now is that I dont know how to configure connection string, in a way that the crm throws no exceptions. Because the AD User can only access the  filter views of the database, I guess need to pass a connection string with login and password. I put the user with the right in the connection string. But it throws an rsprocessaborted error. I believe it is has to do something with the report server right?  Anyone can understand my problem, and may sharing a solution ?

    cheers Steven

    Tuesday, February 2, 2016 9:08 AM

All replies

  • You're right that the issue is that users are not granted SQL permission on the audit table, and there is no filtered view that they can use. There are several ways around this:

    1. You could grant SELECT permission on the table to the AD users who would run the report
    2. You could (depending on how the query is written), create a SQL view that contains the query, and grant users SELECT permission on this view
    3. You could grant users SQL permission to impersonate a login/user, and use the Execute As statement as the first part of the report - note I've not tried this, so it may not work, but I wouldn't recommend it anyway
    4. You could create a separate Data Source in RS, with stored credentials that have rights on the audit table, and configure (in RS), the report to use this Data Source

    Personally I'd go with either no. 2 or no. 4

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Tuesday, February 2, 2016 12:02 PM