With Microsoft Dynamics CRM 2011, accessing directly the CRM database is supported only through the filtered views. This is how dynamic Excel files work by the way.
Recently I had to design a quite complex query involving about 12 inner joins. I wrote the query in SQL and then had to use it in inside a plugin. The problem is that when accessing the CRM database from a plugin, the connection is opened with the identity of the CRMAppPool. If it’s Network Service or another computer account, then normally no data should be accessible through filtered views. If it’s a domain account that also is a CRM system administrator, then potentially too much (ie all) data is available.
The solution I found to work around this issue is to add this command on top of the SQL query:
Execute As User=’domainusername’
The Username is of course retrieved inside the plugin from the context’s userid.
While this worked well, apparently (at least that’s what I understood) this induced another problem which is that the user account under which the query is being run doesn’t necesserily have the right to call the Execute as command, which could apparently happen. And so I started receiving this error:
A severe error occurred on the current command. The results, if any, should be discarded.
So, I added the “Revert” command at the end of the query, which seemed to solve this issue!
And now I have a sql query, using filtered views, that impersonates the plugin context’s userid!
Next time I’ll use Linq…