Access Denied Error

I'm running SQL Server 2000 on a Windows Server 2003 machine. We have sp3 o
n
SQL. I am getting an error when I query against my views. The views use a
linked server that grabs data from FoxPro dbf files located on the same
machine.
In resolving a memory issue, we disabled the 'Allow InProcess' setting on
the OLE DB for ODBC Drivers provider. I don't think this has anything to do
w/ the access denied error because I enabled 'Allow InProcess' to test it an
d
then disabled again when I got the error.
The error message is:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
returned 0x80070005: Access denied.].
The account is a domain account that is part of the administrators group on
the server. It is also a System Administrator role in SQL.
Any help is appreciated. Thanks, PhilPhil,
The obvious things to check are that the account running SQL Server
(which is likely not your Windows account) has the proper permissions
on the files in question, and that you are referring to them in a way that
is correct for that account (not using a drive letter defined at logon for
your own Windows account, for example)
Next, you could try a different way of accessing the linked server data.
Try an alternative among the 4-part name lnksrv.database.owner.table,
OpenQuery, OpenRowSet, or OpenDataSource. If there is another
provider besides MSDASQL, try it.
Beyond that, unfortunately, most newsgroup threads with this question
end with
no resolution. :( My best guess is to try adding this registry key,
though I'm not
certain this works for an arbitrary provider.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Providers\MSDASQL\Disallow
AdhocAccess
Make this a REG_DWORD with value 0.
Here is a link to a few dozen newsgroup threads on this issue - maybe
you'll find something.
http://groups-beta.google.com/group...rface+sqlserver
If you resolve this problem, please let us know!
Steve Kass
Drew University
phil wrote:

>I'm running SQL Server 2000 on a Windows Server 2003 machine. We have sp3
on
>SQL. I am getting an error when I query against my views. The views use a
>linked server that grabs data from FoxPro dbf files located on the same
>machine.
>In resolving a memory issue, we disabled the 'Allow InProcess' setting on
>the OLE DB for ODBC Drivers provider. I don't think this has anything to d
o
>w/ the access denied error because I enabled 'Allow InProcess' to test it a
nd
>then disabled again when I got the error.
>The error message is:
>Server: Msg 7399, Level 16, State 1, Line 1
>OLE DB provider 'MSDASQL' reported an error. Access denied.
>OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
>returned 0x80070005: Access denied.].
>The account is a domain account that is part of the administrators group on
>the server. It is also a System Administrator role in SQL.
>Any help is appreciated. Thanks, Phil
>
>|||Well, I resolved this issue by selecting 'Allow InProcess' for the OLE DB
Provider for ODBC Drivers. However, I had deselected this setting to correc
t
a memory issue I am having. Now, I'm looking to resolve that a different
way. (argh.)
- Phil
"Steve Kass" wrote:

> Phil,
> The obvious things to check are that the account running SQL Server
> (which is likely not your Windows account) has the proper permissions
> on the files in question, and that you are referring to them in a way that
> is correct for that account (not using a drive letter defined at logon for
> your own Windows account, for example)
> Next, you could try a different way of accessing the linked server data.
> Try an alternative among the 4-part name lnksrv.database.owner.table,
> OpenQuery, OpenRowSet, or OpenDataSource. If there is another
> provider besides MSDASQL, try it.
> Beyond that, unfortunately, most newsgroup threads with this question
> end with
> no resolution. :( My best guess is to try adding this registry key,
> though I'm not
> certain this works for an arbitrary provider.
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Providers\MSDASQL\Disall
owAdhocAccess
> Make this a REG_DWORD with value 0.
> Here is a link to a few dozen newsgroup threads on this issue - maybe
> you'll find something.
> http://groups-beta.google.com/group...rface+sqlserver
> If you resolve this problem, please let us know!
> Steve Kass
> Drew University
> phil wrote:
>
>|||An alternative solution is to use the -g switch, which we may pursue. This
is explained in BOL. - Phil
"phil" wrote:

> Well, I resolved this issue by selecting 'Allow InProcess' for the OLE DB
> Provider for ODBC Drivers. However, I had deselected this setting to corr
ect
> a memory issue I am having. Now, I'm looking to resolve that a different
> way. (argh.)
> - Phil
>

Abstracting cross-databases references with views

On our SQL Server, the databases reference each other quite often. eg. UPDAT
E
SalesDatabase..DailySales SET Flag = 1. We are planning to change that so
that all references to other databases are done through views, because we
want to abstract and centralise all the cross-database references.
So we'll end up with
CREATE View as SELECT * FROM SalesDatabase..DailySales
UPDATE DailySales SET Flag = 1
My question is this. By going through a view, will this have any impact on
performance ?
Thanks, CraigGraig
Generally speaking views are not about performance. Views are about
security. If a performance is important to you I'd suggest to use a stored
procedure instead and make sure that you jave properly defined indexes on
the table.
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B6A50E38-BCF3-44F1-9179-DE653A340655@.microsoft.com...
> On our SQL Server, the databases reference each other quite often. eg.
> UPDATE
> SalesDatabase..DailySales SET Flag = 1. We are planning to change that so
> that all references to other databases are done through views, because we
> want to abstract and centralise all the cross-database references.
> So we'll end up with
> CREATE View as SELECT * FROM SalesDatabase..DailySales
> UPDATE DailySales SET Flag = 1
> My question is this. By going through a view, will this have any impact on
> performance ?
> Thanks, Craig|||I do use stored procedures and all the tables are indexed.
What I'm not sure about is whether performance will be affected if instead
of selecting / updating / deleting from a table that exists in another
database, I select / update / delete from a local view that selects from tha
t
table in the other database. The SQL I'm talking about would be in a stored
procedure.
BEFORE:
create proc Proc1 as UPDATE OtherDatabase..Sales SET Flag = 1
AFTER:
create view Sales as SELECT ProductID, SalesQty, SalesAmt, Flag FROM
OtherDatabase..Sales
create proc Proc1 as UPDATE Sales SET Flag = 1
The reason for doing is specific to our applications and that is not what I
am asking. What I need to know is whether doing this will affect perfomance.
An extra question is : would the stored procedure (that calls the local view
that calls the table in the other database) be able to use the indexes in th
e
table or would the view in between prevent that from happening.
Any help would be greatly appreciated,
Craig

About VIEWS

Im new in crystal report. Is there a good tutorial on creating VIews.. how to create or modify.. thanksIm new in crystal report. Is there a good tutorial on creating VIews.. how to create or modify.. thanks

to get the details of view you should search database related topics.

About Security between views and tables

Hi,

I've got a table T1 and a view V1 based on T1's rows (but not all rows).

I've granted users to do update and select on my View V1.
But i don't want users directly update my table T1.

then, i log as a standard user in Enterprise mgr, and open my view V1. I modify a value in a field, and when i validate, an error occurs saying i'm not allowed to make Select neither Update on table T1.

Is there a way of doing this ? (sql 2000).

You can't update a view. A view isn't a table. It's just a select statement.|||

Yes i can. I did it many times, as the select is not too complex, you can do it.

For insance, if you got a table T1 with fields (ID, f1,f2,f3,f4) and a view V1 as select id, f1,f2 from t1, you 'll be allowed to make updates.

this feature works fine with many views i wrote.

|||Well, I'll be...

Have you read through this document to see if you have met the criteria for updateable views? http://msdn2.microsoft.com/en-gb/library/ms187956.aspx|||

thanks for the link. yes my view is updateable, and when there's no grant restrictions, it works fine.

My problem is the following : I'm working on an ms access migration to sql 2000.
Then, i use my view as an attached table in access, an users can modify some values.
But i don't wan basic users have a direct access to the table, only to the view.
That's why i tried to restrict select and update on the table, whereas select and updates are allowed to the view.

But i'm afraid i won't be able to do this...

|||If you have the base table restricted, I'm wondering if the view can supercede that security. I'm thinking not.|||

i'm afraid you're right. so i'll have to manage my own security... :-(