Home > Archive > SQL Server Programming > February 2005 > Using a view in an SP which has a specific sql user
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Using a view in an SP which has a specific sql user
|
|
|
| Hi,
I'm writing sproc based on server A, which during processing needs to create
a select statement with a join from a view on server B. I only have access to
the view on server B through use of a specific sql login, which I obviously
also have the password for. The sql login and password on server B is not the
login that will be using the sproc on server A, since that login does not
have access to server A.
How do I create the join in this case? i.e. run a select statement on Server
A which joins with a view on server B with a specific sql login?
Hopefully I have explained that OK ;)
Many thanks in advance,
Andy
| |
|
| I forgot, when I try openquery, or creating a linked server I get the
following...
(server name & password etc have been changed to protect the innocent)
SELECT *
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=Yew01;UID=theUser;PWD=the
password',
theDB.dbo.vw_theView) AS fred
GO
returns:
Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access
this provider through a linked server.
and,
EXEC sp_addlinkedserver
@server = 'theServer',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL
Server};SERVER=theServer;UID=theUser;PWD
=thePassword;'
GO
returns:
Server: Msg 15247, Level 16, State 1, Procedure sp_addlinkedserver, Line 26
User does not have permission to perform this action.
|
|
|
|
|