Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Filtered View access - SQL Login RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I'm trying to use a SQL login to create a Crystal Report accessing data from Microsoft CRM .Using the 'sa' user as a starting point What I'm finding is that using Microsoft's supported route of accessing the CRM data through the Filtered Views returns no rows, presumably because the 'sa' user is not a CRM user. Accessing the tables directly (e.g. AccountBase) is fine. Is there any way of accessing the CRM data through the Filtered Views, which is using a SQL login on the CRM server?

    Also just to confirm the 'sa' loging had dbo access rights to the CRM database.
    Tuesday, March 9, 2010 11:00 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    In short, no.  The filtered views use the context of the logged in user and incorporate the permissions based on that user.  Since sa is not a CRM user, then no rows are returned.  Further, using any SQL Login will also return no rows from the filteredviews.  What you can do is query the non-filtered views, e.g. account.  This is the same columns as in the filtered view but without regard for which records the user is allowed to see.

    Tuesday, March 9, 2010 11:16 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    You are running into a problem because the filteredacount view does not join to the stringmap table, it joins to the filteredstringmap view, which implements the same security as the filteredaccount view.
    Wednesday, March 10, 2010 4:53 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    In short, no.  The filtered views use the context of the logged in user and incorporate the permissions based on that user.  Since sa is not a CRM user, then no rows are returned.  Further, using any SQL Login will also return no rows from the filteredviews.  What you can do is query the non-filtered views, e.g. account.  This is the same columns as in the filtered view but without regard for which records the user is allowed to see.

    Tuesday, March 9, 2010 11:16 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for the response.

    What I have done is copied the FilteredAccount view script however taking out the WHERE clause at the end that looks at privledges for a user.  This displays all the same data and I am happy that this works.  The only problem I am experiencing now is getting the picklist value for a field.  So let me give you an example why I wanted to access the filtered views in the first place and not the AccountBase table.

    I have a field (FIELDA) with picklist values (Customer, Prospect, Supplier, etc).  The AccountBase table will show this as:

    FIELDA
    1
    2
    3
    1

    The FilteredAccount View will show this data as

    FIELDA      FIELDNAME
    1               Customer
    2               Prospect
    3               Supplier
    1               Customer

    Now the Filtered View which I copied for FilteredAccount (taking out the privledges clause) shows me the data but still does not show the name of the value.  So basically is displaying as

    FIELDA            FIELDNAME
    1                          NULL
    2                          NULL
    3                          NULL
    1                          NULL

    So the only data it is not generateing is the name of the picklist values but aprt from that everything else is fine.  The Picklist values are stored in the StringMap table and this is what is causing the problem.  If I just query the StrinMap table it shows the data fine.

    Thanks
    Tuesday, March 9, 2010 11:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    If the customer will give you the sa login for sql, wouldn't they be willing to give you the crmadmin and password for use in your report?  If not, maybe they would be willing to create a CRM user record and give it sys admin rights so you could use that one so you don't have to keep working around the issues?
    Best Regards | Twitter: edwardsdna
    Wednesday, March 10, 2010 2:11 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    You are running into a problem because the filteredacount view does not join to the stringmap table, it joins to the filteredstringmap view, which implements the same security as the filteredaccount view.
    Wednesday, March 10, 2010 4:53 PM