question

S_M avatar image
S_M asked

Cannot read the data from a view, but can from the underlying tables.

Hello All, This sounds a bit wierd, but need some help in resolving this issue. I am a new DBA at our organization and having issues to read data from a view. This view was created by the BI team for reporting, which they say they can read the data but when i am trying to do the same i cannot fetch any rows at all, neither from VIEW or the SELECT query. Overview of the SQL view from the SELECT query. Reporting_view_name uses 4 other views & a table to populate the data Reporting_view_name - Cannot read the data VIEW1 - Cannot read the data VIEW2 - Cannot read the data VIEW3 - Can read the data TABLE1 - Can read the data VIEW4 - Can read the data VIEW1 uses 4 tables & 1 view and i can read the data from those 4 tables but not the view, and this view further uses two other tables and i can read the data from them as well. VIEW2 uses 4 tables and i can read from all 4 and 3 same which are being used in VIEW1 VIEW3 uses another VIEW to get data VIEW4 uses 2 tables to get the data Any ideas will be very helpful
sql-server-2008-r2viewdata
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@S_M you must be using the impersonation with "EXECUTE AS USER" clause, if you have cross database queries then you must use "EXECUTE AS LOGIN" instead.
1 Like 1 ·
I guess being the DBA you should have the sysadmin rights? Which user you are using especially when you say you cannot read the data from one table? Do you see the results, if you impersonate BI Team's user by using "Execute as"?
0 Likes 0 ·
When you say you can't read the views, does it mean your SELECT-SQL-statement executes fine and returns 0 rows or do you get an error when selecting from the view? If you get an error message: What is it?
0 Likes 0 ·
Hello Usman, That's right, as a DBA i have the sysadmin rights. But not sure what the case might be here. I tried impersonating as BI user, but the user does not have access to one other database where one of the table is being called from.
0 Likes 0 ·
Hi Magnus, That's exectly what is happening. I am executing the view and as well as the select statement which the view is using and both are completing the execution finr but returns as 0 rows affected. No errors whatsoever....
0 Likes 0 ·
Show more comments
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Are there any WHERE-clauses in the views, filtering based on user_name(), suser_sname() function or similar? That's a common thing, that a view is created in a system, giving users only the rows they are allowed to see. And as a DBA, you are probably not configured to see data (since you can get the data directly from the tables directly anyway).
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Magnus Ahlkvist +1. Yes, my initial thought was also the same that it could be because of some sort of Row level security implementation, but the statement "VIEW1 uses 5 tables and i can read the data from 4 of them" made me think otherwise. Besides, if the OP can see the data by impersonating the specified BI user, then it will be confirmed :)
0 Likes 0 ·
S_M avatar image
S_M answered
Yes @Usman i checked the script for the dependent views and how they were written but was not so impressed by them and suggested to tune them a bit or re-write again. Thanks for your help @Magnus Ahlkvist and @Usman Butt
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.