Solved

Filtered Views in SQL Return nothing

Posted on 2008-10-02
7
1,589 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

622 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