Solved

Filtered Views in SQL Return nothing

Posted on 2008-10-02
7
1,456 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
set default date format in mssql to mm/dd/yyyy 22 68
SQL Server Deadlocks 12 49
SQL Syntax join to include values from first table 3 27
SQL Server Question 5 25
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

16 Experts available now in Live!

Get 1:1 Help Now