Permissions on views

Although I have granted select permissions on the views in my database that
are the recordesource for reports in a visual basic application, I cannot
open the reports from the application. I get the VB error 1005 ("can't open
recordset"). It would seem to be a permissions problem because everything
else in the app works fine except the reports that are based on views. I
haven't found anything in Books on Line that has solved the problem. The D
B
was developed in sql 2000 and the compatability level for this DB in sql 200
5
is 80. I'm grateful for any help.Pam
Make sure that you connect with the "right" user from application to run
reports.
Why do you have 80 compatibilty level for SQL Server 2005?
"Pam Davey" <PamDavey@.discussions.microsoft.com> wrote in message
news:16199EB3-EC0B-4A5E-B68F-1EEF73DDDF60@.microsoft.com...
> Although I have granted select permissions on the views in my database
> that
> are the recordesource for reports in a visual basic application, I cannot
> open the reports from the application. I get the VB error 1005 ("can't
> open
> recordset"). It would seem to be a permissions problem because everything
> else in the app works fine except the reports that are based on views. I
> haven't found anything in Books on Line that has solved the problem. The
> DB
> was developed in sql 2000 and the compatability level for this DB in sql
> 2005
> is 80. I'm grateful for any help.|||Hi!
Create view with view_metadata attribute.
Micle.
"Pam Davey" <PamDavey@.discussions.microsoft.com> wrote in message
news:16199EB3-EC0B-4A5E-B68F-1EEF73DDDF60@.microsoft.com...
> Although I have granted select permissions on the views in my database
> that
> are the recordesource for reports in a visual basic application, I cannot
> open the reports from the application. I get the VB error 1005 ("can't
> open
> recordset"). It would seem to be a permissions problem because everything
> else in the app works fine except the reports that are based on views. I
> haven't found anything in Books on Line that has solved the problem. The
> DB
> was developed in sql 2000 and the compatability level for this DB in sql
> 2005
> is 80. I'm grateful for any help.|||Hi Uri-
Thank you. I am connecting with the correct user. I have the database of
interset that resides on SQL Server 2005 set to compatability level 80 so
that it will have backward compatability with SQL server 2000 on which it wa
s
developed.
"Uri Dimant" wrote:

> Pam
> Make sure that you connect with the "right" user from application to run
> reports.
> Why do you have 80 compatibilty level for SQL Server 2005?
>
> "Pam Davey" <PamDavey@.discussions.microsoft.com> wrote in message
> news:16199EB3-EC0B-4A5E-B68F-1EEF73DDDF60@.microsoft.com...
>
>|||Hi Micle-
Thank you for your input. Unfortunely, it didn't seem to make any
difference. I get the same error. Oddly, I can get to all the underlying
tables that make up the view. I just can't get to the view, even when it's
created as you suggested.
"Micle" wrote:

> Hi!
> Create view with view_metadata attribute.
> Micle.
>
> "Pam Davey" <PamDavey@.discussions.microsoft.com> wrote in message
> news:16199EB3-EC0B-4A5E-B68F-1EEF73DDDF60@.microsoft.com...
>
>|||Pam Davey (PamDavey@.discussions.microsoft.com) writes:
> Although I have granted select permissions on the views in my database
> that are the recordesource for reports in a visual basic application, I
> cannot open the reports from the application. I get the VB error 1005
> ("can't open recordset"). It would seem to be a permissions problem
> because everything else in the app works fine except the reports that
> are based on views. I haven't found anything in Books on Line that has
> solved the problem. The DB was developed in sql 2000 and the
> compatability level for this DB in sql 2005 is 80. I'm grateful for any
> help.
Do the reports work when you run it on SQL 2000? Hav you verified that the
queries work when you run them from Query Analyzer or Management Studio.
Could you post the code you are using?
I would not expect a permissons problem, unless you are doing a poor job
of handling errors from SQL server. Nevertheless, here is a kind of shot
in the dark that you can try:
GRANT VIEW DEFINITION ON SCHEMA::dbo TO <user>
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your help Erland. Yes, the queries work in SQL 2000 as well as
when run from the Query Analyzer and Management Studio.
There's very little code actually. I have a 3rd party report control from
Component One that renders reports from report definitions stored in an xml
file. I've checked the xml file and the various report definitions within th
e
file have the correct queries named as their recordsource.
Thanks for the "GRANT..." thought. Didn't make a difference though. Still, I
appreciate your help.
"Erland Sommarskog" wrote:

> Pam Davey (PamDavey@.discussions.microsoft.com) writes:
> Do the reports work when you run it on SQL 2000? Hav you verified that the
> queries work when you run them from Query Analyzer or Management Studio.
> Could you post the code you are using?
> I would not expect a permissons problem, unless you are doing a poor job
> of handling errors from SQL server. Nevertheless, here is a kind of shot
> in the dark that you can try:
> GRANT VIEW DEFINITION ON SCHEMA::dbo TO <user>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Pam Davey (PamDavey@.discussions.microsoft.com) writes:
> Thanks for your help Erland. Yes, the queries work in SQL 2000 as well as
> when run from the Query Analyzer and Management Studio.
> There's very little code actually. I have a 3rd party report control
> from Component One that renders reports from report definitions stored
> in an xml file. I've checked the xml file and the various report
> definitions within the file have the correct queries named as their
> recordsource.
> Thanks for the "GRANT..." thought. Didn't make a difference though.
> Still, I appreciate your help.
I'm afraid that there is very little to work on. Maybe the best is to
contact the vendor.
All I can really suggest is to use Profiler to eavesdrop on what the
report tool sends to SQL Server. You can include Error events in
the trace, so you can see if any errors are reported.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Okay, I'll give it a try. Thank you again.
"Erland Sommarskog" wrote:

> Pam Davey (PamDavey@.discussions.microsoft.com) writes:
> I'm afraid that there is very little to work on. Maybe the best is to
> contact the vendor.
> All I can really suggest is to use Profiler to eavesdrop on what the
> report tool sends to SQL Server. You can include Error events in
> the trace, so you can see if any errors are reported.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||"Pam Davey" <PamDavey@.discussions.microsoft.com> wrote in message
news:DEB58306-AC92-45FA-A657-A08EA6CD6D2A@.microsoft.com...[vbcol=seagreen]
> Okay, I'll give it a try. Thank you again.
> "Erland Sommarskog" wrote:
>
A good reality check would be to execute the application under an account
that's an admin (as in NT account that is a member of the sysadmin fixed
server role) on the SQL box. If it still doesn't work, the reason is not
permissions! If it does work under admin privileges, the next reality check
might be to grant all to guest; if that works revoke the last grant and
grant all to public.
Another tactic would be to write a quickie script to open a recordset on
that view, and execute it from an end-user-level login context -- that will
allow you to see any error output that the report control may be eating.
Divide and conquer, that's the game. :-)
Good Luck,
Mark
[vbcol=seagreen]