locked
Can't select from Filtered views? RRS feed

  • Question

  • I query from FilteredAccount as a particular user and I get nothing (no rows), but I can query Account just fine.  I've tried making the user db_owner, still same behavior.  I'm fascinated by how this is even possible, but is there something crazy fancy going on with Filtered views and permissions?
    Wednesday, June 24, 2009 1:45 PM

Answers

All replies

  • Asking a question seems all to often to bring out the answer to yourself... I see that the filtered views use the current user id in some joins - since the user (a domain account for reporting services) doesn't have one - kapoof.  I assume I'm supposed to create a read-only account for that user.
    Wednesday, June 24, 2009 1:48 PM
  • Yes, and Microsoft probably is not too happy with your trying to affect db ownership.  The database is supposed to be off-limits.
    Wednesday, June 24, 2009 2:10 PM
  • Yeah, I undid that, was just testing.
    Wednesday, June 24, 2009 3:09 PM
  • What sql are you using to select from filteredaccount as a particular user?
    Wednesday, June 24, 2009 4:57 PM
  • A fundamental aspect of filtered views is that they return the same data that user would see in CRM. So, if you query them under the context of a non-CRM user, you get no data.

    One workaround is to use SQL impersonation with Execute As. See http://mscrmuk.blogspot.com/2007/11/syntax-issues-using-sql-execute-as-and.html and associated links


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    Wednesday, June 24, 2009 8:52 PM
    Moderator
  • What sort of CAL if any is needed for these "system" users?  For write authority do I have to impersonate or waste a CAL?
    Wednesday, June 24, 2009 9:38 PM
  • I was just using "SELECT * FROM FilteredAccount" to see if anything came up, and before making the CRM user it did not.  The latest hiccup, which I'll record here for posterity, is that I had to login to CRM with this new reporting-only account to get its UILanguageId set - this seems a little gross, I should be able to set that on the accounts behalf somewhere in the interface.

    The point of all this craziness is that I now have both SSRS and CRM reports running happily alongside each other from a single Visual Studio dev project/codebase including deployment and IFD.  This allows me to have both subscriptions/scheduled reports and CRM integration.  I'll try to write up the whole boondongle over the next days.
    Thursday, June 25, 2009 1:36 AM
  • I said I'd write it up - so here 'tis SSRS and CRM
    Tuesday, June 30, 2009 5:13 PM
  • If you are using CRM 4.0 you have the ability to create a CRM user and specify that user as a "read-only" user and save a the cost of a CAL. I believe this was set up specifically for thos ecases where you need to create a CRM user just for reading data that is meant to be part of a integration to another system.
    Thursday, July 2, 2009 1:53 AM