Solved

Filtered Views in SQL Return nothing

Posted on 2008-10-02
7
1,539 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

732 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