Given the situation below, can I specify that the pre-configured linked server login should not be used for a particular remote stored procedure call?
I have two SQL Server instances:
[LOCAL]running SQL Server 2012 with database
[REMOTE]running SQL Server 2008R2 with database
[REMOTE] is already set up as a linked server on
[LOCAL]. All users who can access
[LOCAL].[A] can access
[REMOTE].[B] (though not all its objects) through the SQL Server login
GenericUser, which is already set up through a command on
[LOCAL] such as:
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'REMOTE', @useself = 'FALSE', @rmtuser = N'GenericUser', @rmtpassword = N'TheGenericUserPassword'
This login works fine. For authorization reasons, however, I would like to restrict access to a stored procedure
[REMOTE].[B] to one particular Windows Authentication user,
[REMOTE].[B].[dbo].[RemoteSP] will be called from within a stored procedure
[MYDOMAIN\OneUser] may execute (as can other users, who will not call
[RemoteSP], as shown below).
The local stored procedure looks like:
CREATE PROCEDURE [dbo].[LocalSP] @Id INT AS SET NOCOUNT ON -- Do some stuff, then: IF SUSER_NAME() = N'MYDOMAIN\OneUser' BEGIN -- This syntax is invalid: EXEC [REMOTE].[B].[dbo].[RemoteSP] @MyVar = @Id AS SELF END
Access is granted (e.g., by
GRANT EXEC ON [dbo].[RemoteSP] TO [MYDOMAIN\OneUser]) on
[REMOTE], and the user
[MYDOMAIN\OneUser] exists on
[REMOTE] and in
WITH EXECUTE AS 'MYDOMAIN\OneUser' to the
[RemoteSP] definition, as this answer suggests, is unsatisfactory because I still need only
[MYDOMAIN\OneUser] to have access to the stored procedure. Although this question may be somewhat related, it is unanswered, and the linked chat room has been deleted.
I do not want to change the linked server login that
[MYDOMAIN\OneUser] would use for all queries, just this one. I also would prefer to avoid a syntax like:
EXEC (N'EXEC [B].[dbo].[RemoteSP] @MyVar = ?', @Id) AS LOGIN = N'MYDOMAIN\OneUser' AT [REMOTE]
if for no other reason than there is little reason to use a dynamic string -- I have the stored procedure name, etc.