Solved

Filtered Views in SQL Return nothing

Posted on 2008-10-02
7
1,472 Views
Last Modified: 2008-10-14
I'm triing to run from SQL Server Express Manager a "FilteredAccount" view by right clicking the view and going to "Open View". When i do it returns no data.

Normally i know when you run into this problem its because your logged in using a sql account instead of a Active Directory account. I am currently logged in using my AD account (Win Auth) and even when i run the SQL Profiler it returns that i'm accessing the view using that account(AD account).

If in CRM, account information is available to me .
If in SQL CRM db, i can access any table i need to.
If in SQL CRM db, i can access any "Custom" or "NonFiltered" view i need to.

If in SQL CRM db, i cannot access "Filtered" views even logged in using my AD account, my question is, why? What don't i have set that would allow me to view these "Filtered" views?
0
Comment
Question by:bufordk
  • 4
  • 3
7 Comments
 
LVL 15

Expert Comment

by:WilyGuy
ID: 22626737
Have you tried using the EXECUTE AS USER = 'domain\user' before the SQL SELECT?

I know the profiler told you that you were using the right user, but this should set it within the request.... (for sure).

Also, was any change made to the login name after addition to CRM? (check it against the SystemUser table.)
0
 

Author Comment

by:bufordk
ID: 22636566
EXECUTE AS USER = domain\user
select * from filteredaccount

it returns nothing.

EXECUTE AS USER = domain\user
select * from accountbase

it returns the data

The login name in the SystemUserBase Table is fine no change(if thats the table you mean).

0
 
LVL 15

Expert Comment

by:WilyGuy
ID: 22644206
Your initial post indicates SQL Express?  Are you trying to work with the offline client database?  I am unsure how that is configured permission wise.

If we are talking about the regular CRM database, here are some thoughts:
Open up SQL Studio and look at the properties for the filtered view.... (should be a giant SQL Query.)  You are looking for the join to the SystemUser table.  You might try copying the entire query and pasting into a query.  You may find your issue.

If the user in question is not the owner, there is a section further down for "object shared to the user"

You'll also want to look at the permissions and you should see that ReportingGroup and Network Service have SELECT rights.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:bufordk
ID: 22649237
I am looking at the original CRM db, I'm using SQL Express Management Studio to access the database in SQL Server 2005. The one thing i'm unsure of is if I'm a member of the ReportingGroup.

I've looked at the query and found that it points to the SystemUser table but doesnot contain information on specifics there.
0
 
LVL 15

Expert Comment

by:WilyGuy
ID: 22649387
If you are a user in CRM, you SHOULD be in the Reporting Group.

Did you try to execute the Query directly in SQL?
0
 

Author Comment

by:bufordk
ID: 22662988
Yes and it returns nothing (0 rows affected). I'm a member of the ReportingGroup also.
0
 

Accepted Solution

by:
bufordk earned 0 total points
ID: 22677717
I found the issue, it seems that an employee before me had the same first letter of their first name and we shared the last name. When that person left the company we disabled the account. When i came aboard they didn't put the association together and they created a new account using the same username. CRM was  looking at the disabled account and returning that i wasn't an active account(although access to CRM worked fine). Since i was a dbo on the database i was able to access the tables and other views using those permissions. dbo's do not have permissions to "filtered" views data but they do have access to the design.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now