May 20, 2013 at 10:23 am
Ok,
This is driving me crazy.
The security configuration for Linked Server on the local one is as follow:
On Local server login to remote server login mappings:
UserA to UserA
For a login not defined in the list above, connections will
"Be made using this security context" .... and remote login is "sa"
The Linked Server works fine for regular queries, but a non client database is being moved out to the 2nd server. Now a local store procedure that points to the database that it's being moved cannot be altered, not even by me. When I run the following, using my own DBA account ...
CREATE PROCEDURE ABC
AS
set nocount on;
SELECT TOP 1
[col2]
FROM [RemoteServer].[dbName].[dbo].
where col1 = something
GO
I get this error:
The OLE DB provider "SQLNCLI" for linked server "xxxxxx" does not contain the table "MyTable here". The table either does not exist or the current user does not have permissions on that table.
If I open a query window and connect to local server using "UserA", I can run the query inside and see the remote table and data set. However, I can't locally alter or create the store procedure with that remote query. I know it's because the LinkedServer credentials, but how can I solve this without adding extra logins or myself to the LinkedServer properties?
I want to minimize the security risk of connecting or running queries across servers using elevated privileges. "UserA" has limited privileges on the remote server. And "sa" on remote server is not actually "sa". It has limited privileges as well. It uses same name, but that's because it is encoded on the app, so I tricked the system that way.
Any hints?
May 20, 2013 at 12:39 pm
Previous post was due lacked of coffee! lol ... plus a long morning at work.
I added myself (temporary) to the LinkedServer security properties. altered the store procedure, and then removed.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply