Solved

Filtered Views in SQL Return nothing

Posted on 2008-10-02
7
1,439 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
crm 2010 on premise 5 31
sql query 7 37
MS SQL 2016 from Database to Datawarehouse 6 37
SQL Split character from numbers 3 18
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

744 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

13 Experts available now in Live!

Get 1:1 Help Now