access denied to access a table in another server

Hi, Here is the select stament:
SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
Server};SERVER=Testbox;UID=USersql;PWD=123456', Testdb.dbo.Testtable)
Testtable
WHERE (State= 'NY')
When I run it in sql query analyzer, I got this error: Ad hoc access to OLE
DB provider 'MSDASQL' has been denied. You must access this provider through
a linked server.
If I login in sysadmin into sql query analyzer, I was able to get result
sets.
My question is that the user who runs this statement have to be sysadmin? or
there is another way around it?
Thanks,The user running the query has to be mapped in the link Server for him\her to
be able to run this. My experince was that user (SQL side) , has to be mapped
to Admin or someone with read permission in order to do this.
If you right click on Linked Server and click on Security, you can do the
mapping.
Thank you
"mecn" wrote:
> Hi, Here is the select stament:
> SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
> Server};SERVER=Testbox;UID=USersql;PWD=123456', Testdb.dbo.Testtable)
> Testtable
> WHERE (State= 'NY')
> When I run it in sql query analyzer, I got this error: Ad hoc access to OLE
> DB provider 'MSDASQL' has been denied. You must access this provider through
> a linked server.
> If I login in sysadmin into sql query analyzer, I was able to get result
> sets.
> My question is that the user who runs this statement have to be sysadmin? or
> there is another way around it?
> Thanks,
>
>|||Thanks,
The select statement from an app. If the sql statement can't be change.
there is any ways around? to have that user select data from another sql
server without linking the server? and without being sysadmin? read only
access won't work.
Thanks,
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:D8B173EC-CD9E-439D-860B-E0A68380EB13@.microsoft.com...
> The user running the query has to be mapped in the link Server for him\her
> to
> be able to run this. My experince was that user (SQL side) , has to be
> mapped
> to Admin or someone with read permission in order to do this.
> If you right click on Linked Server and click on Security, you can do the
> mapping.
> Thank you
>
> "mecn" wrote:
>> Hi, Here is the select stament:
>> SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
>> Server};SERVER=Testbox;UID=USersql;PWD=123456', Testdb.dbo.Testtable)
>> Testtable
>> WHERE (State= 'NY')
>> When I run it in sql query analyzer, I got this error: Ad hoc access to
>> OLE
>> DB provider 'MSDASQL' has been denied. You must access this provider
>> through
>> a linked server.
>> If I login in sysadmin into sql query analyzer, I was able to get result
>> sets.
>> My question is that the user who runs this statement have to be sysadmin?
>> or
>> there is another way around it?
>> Thanks,
>>|||If you want to connect to another SQL Server, you would need the SQL Server
linked. However, If you are running the query through an app, you can map the
login name used by the app to from Server1 to login name used by server2.
We have link server logins from Server1 to Sever2 that has limited access
such as read only. From the error message listed above, it looks like your
logins are not mapped correctly.
1) Are you connection from SQL Server to SQL Server?
2) What application are you using
3) What are your logins, and what permission do you have for each
This is how we generally connect to severs and I am not sure if there are
other ways to do that..
"mecn" wrote:
> Thanks,
> The select statement from an app. If the sql statement can't be change.
> there is any ways around? to have that user select data from another sql
> server without linking the server? and without being sysadmin? read only
> access won't work.
> Thanks,
>
> "bluefish" <bluefish@.discussions.microsoft.com> wrote in message
> news:D8B173EC-CD9E-439D-860B-E0A68380EB13@.microsoft.com...
> > The user running the query has to be mapped in the link Server for him\her
> > to
> > be able to run this. My experince was that user (SQL side) , has to be
> > mapped
> > to Admin or someone with read permission in order to do this.
> >
> > If you right click on Linked Server and click on Security, you can do the
> > mapping.
> > Thank you
> >
> >
> > "mecn" wrote:
> >
> >> Hi, Here is the select stament:
> >>
> >> SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
> >> Server};SERVER=Testbox;UID=USersql;PWD=123456', Testdb.dbo.Testtable)
> >> Testtable
> >> WHERE (State= 'NY')
> >>
> >> When I run it in sql query analyzer, I got this error: Ad hoc access to
> >> OLE
> >> DB provider 'MSDASQL' has been denied. You must access this provider
> >> through
> >> a linked server.
> >>
> >> If I login in sysadmin into sql query analyzer, I was able to get result
> >> sets.
> >>
> >> My question is that the user who runs this statement have to be sysadmin?
> >> or
> >> there is another way around it?
> >>
> >> Thanks,
> >>
> >>
> >>
>
>|||Thanks all,
There is an internal dev app connected to server one one of the procedure is
to retrieverecords from one table in a diff server. My question is linked
server is the only option? if not do I have to setup server one user for the
app as sysadmin in order to get records from a table in diff server b?
Thankls,
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%237OAr7FGGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hi, Here is the select stament:
> SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
> Server};SERVER=Testbox;UID=USersql;PWD=123456', Testdb.dbo.Testtable)
> Testtable
> WHERE (State= 'NY')
> When I run it in sql query analyzer, I got this error: Ad hoc access to
> OLE DB provider 'MSDASQL' has been denied. You must access this provider
> through a linked server.
> If I login in sysadmin into sql query analyzer, I was able to get result
> sets.
> My question is that the user who runs this statement have to be sysadmin?
> or there is another way around it?
> Thanks,
>|||Okay, Let's call your dev app server Sever-A, and the Application App-Test.
Now App_Test connects to server-A using a login 'Abc' right?
Let's call the secound servel Server-B. Let say you have a login 'CDE' that
has read/write permission to your table.
What you have to do is link the 'Sever-A' and 'ServerB'.
Then map 'ABC' login ( this does not have to be sysadmin or dbo) to 'CDE'
through the link server, so when you run the query through the app login is
authenticate.
Let me know if that works.|||Thanks Bluefish,
Now I could get the records from another server under following statement:
SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
Server};SERVER=Testbox;UID=cde;PWD=123456', Testdb.dbo.Testtable)
Testtable
WHERE (State= 'NY')
Under 1 condition: the user abc has to be sysadmin in server_A in order to
do so.
There is a way that NOT set user ABC as sysadmin to get the records from
server_B by using above connection string without linking that 2 servers.
Thanks,
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:8AE14C5B-A649-40C7-A3C3-0DC131B33167@.microsoft.com...
> Okay, Let's call your dev app server Sever-A, and the Application
> App-Test.
> Now App_Test connects to server-A using a login 'Abc' right?
> Let's call the secound servel Server-B. Let say you have a login 'CDE'
> that
> has read/write permission to your table.
> What you have to do is link the 'Sever-A' and 'ServerB'.
> Then map 'ABC' login ( this does not have to be sysadmin or dbo) to 'CDE'
> through the link server, so when you run the query through the app login
> is
> authenticate.
> Let me know if that works.
>