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]

Permissions granted from Domain credentials

I am working with Visual Studio.net and creating a front end for a SQL datab
ase. Now with the access front end, it uses the domain credentials of the l
ogged in user to determine what permissions they have for editing records in
the SQL (2000) database.
I need the same to be done with the web front end I am creating now. I have
gone into the WEB.CONFIG file and put in my credentials there, identity i
mpersonate="true" userName="domain\johndoe" password="password"
Now this is ok if...I didn't change my password regularly and I wanted every
one to have full access...but obviously I don't. Is there some syntax that
I need there so that it will use the credentials of the logged in user? Or a
m I in the wrong file and s
hould be making changes elsewhere? I am new to this but I am learning. In
the OleDB connection for the datagrid on the page, I have added the line...
Trusted Connection=SSPI. What am I missing? I am using OleDb connection a
nd OleDb Command. Like I s
aid...with my credential in the web.config, it works fine. But that is only
good for now. I need the security set so when I get to creating a page tha
t only administrators have access to I can get those to work correctly.
Thanks All!!I am assuming that with the Trusted Connection = SSPI the connection is
failing. If so, what error message do you get when it fails?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||You need to allow Kerberos Authentication to occur. The middle machine
must be trusted for Security Delegation. Also , the SQL Server service
account needs to have the Service Principal Name set.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Permission to access dB problem in SQL Server 2005 Express from inside VS2005

I am trying to program a database from inside C++.NET via Visual
Studio 2005 using the ADO.NET set of classes, but this I believe is a
SQL Server 2005 Express permissions question under Windows XP
Professional OS.
In another thread, http://tinyurl.com/373lzx, I was able to open a
database successfully using VS2005 (thanks to a suggestion by Erland
Sommarskog). But subsequent to this I played around with the local
policy permissons while logged in as Administrator and may have
blocked access to this database. How to undo this? (I forgot what I
changed, but at the time it was pretty innocuous, and none of my other
programs have been affected).
Two problems really:
0/ though I can create and access a new database as a Power User, I
cannot access the old database anymore. Literally I have a red 'x'
next to the icon for the old database, and I get the error, under the
Server Explorer of Visual Studio 2005, when I right click "refresh" of
"Exception of the type 'System.Exception' was thrown".
1/ Switching to Administrator is no help (see below for the error),
but that also raises another question (see Q2 below)
2/ I played around with SQL Server 2005 Express Manager,and tried
adding various groups, but to no avail.
Question #1: how to undo this
Question #2: how to add "Admin" as an "object" or whatever you call
it under the SQL Server 2005 Express manager so I avoid the below
error (i.e., so Admin can access this dB).
Thank you.
RL
-
No connection the following error occurred: Unable to open the
physical file "C
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
\NEWPC_USERDCV_DB.mdf". Operating system error 32: "32(The process
cannot access the file because it is being
used by another process.)".
Unable to open the physical file "C:\Program Files\Microsoft SQL Server
\MSSQL.1
MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF". Operating system error 32:
"32(The process canot access the file because it is being used by
another process.)".
Cannot open database "NEWPC_USERDCV_DB" requested by the login. The
login failed.
Login failed for user 'PC_NAME-PC_USER\Administrator'.
File activation failure. The physical file name "C:\Program Files
\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF" may be incorrect.
Press any key to continue . . .This article may help.
http://support.microsoft.com/kb/313222/en-us
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"raylopez99" wrote:
>I am trying to program a database from inside C++.NET via Visual
> Studio 2005 using the ADO.NET set of classes, but this I believe is a
> SQL Server 2005 Express permissions question under Windows XP
> Professional OS.
> In another thread, http://tinyurl.com/373lzx, I was able to open a
> database successfully using VS2005 (thanks to a suggestion by Erland
> Sommarskog). But subsequent to this I played around with the local
> policy permissons while logged in as Administrator and may have
> blocked access to this database. How to undo this? (I forgot what I
> changed, but at the time it was pretty innocuous, and none of my other
> programs have been affected).
> Two problems really:
> 0/ though I can create and access a new database as a Power User, I
> cannot access the old database anymore. Literally I have a red 'x'
> next to the icon for the old database, and I get the error, under the
> Server Explorer of Visual Studio 2005, when I right click "refresh" of
> "Exception of the type 'System.Exception' was thrown".
> 1/ Switching to Administrator is no help (see below for the error),
> but that also raises another question (see Q2 below)
> 2/ I played around with SQL Server 2005 Express Manager,and tried
> adding various groups, but to no avail.
> Question #1: how to undo this
> Question #2: how to add "Admin" as an "object" or whatever you call
> it under the SQL Server 2005 Express manager so I avoid the below
> error (i.e., so Admin can access this dB).
> Thank you.
> RL
> -
> No connection the following error occurred: Unable to open the
> physical file "C
> \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
> \NEWPC_USERDCV_DB.mdf". Operating system error 32: "32(The process
> cannot access the file because it is being
> used by another process.)".
> Unable to open the physical file "C:\Program Files\Microsoft SQL Server
> \MSSQL.1
> MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF". Operating system error 32:
> "32(The process canot access the file because it is being used by
> another process.)".
> Cannot open database "NEWPC_USERDCV_DB" requested by the login. The
> login failed.
> Login failed for user 'PC_NAME-PC_USER\Administrator'.
> File activation failure. The physical file name "C:\Program Files
> \Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF" may be incorrect.
> Press any key to continue . . .
>|||On Apr 8, 11:04 am, "Dave Patrick" <DSPatr...@.nospam.gmail.com> wrote:
> This article may help.
> http://support.microsoft.com/kb/313222/en-us
>
Thanks but I was thinking of a less extreme fix.
I'm thinking there must be some way of 'unlocking' the old database.
The reason is, I figured out a workaround, in that I constructed the
same database from scratch, from inside of VS 2005 (i.e. mechanically
reconstructing the entire database), and the new, identical database
can now be accessed as before, from inside VS 2005, and the connection
works (refresh works, 'test connection' from Modify also works, and
the program accessing the new database). But the old database still
has a red "X" next to it, and refreshing does not work.
Just a weird aspect.
I've ordered some books on dB programming and maybe they'll have some
insight, but in general I'd like to know how to reset permissions and/
or fix such problems in the future (short of reconstructing the
database 'from scratch' as I did here)
RL