3

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:

  1. [LOCAL] running SQL Server 2012 with database [A] and
  2. [REMOTE] running SQL Server 2008R2 with database [B].

[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 [dbo].[RemoteSP] on [REMOTE].[B] to one particular Windows Authentication user, [MYDOMAIN\OneUser]. [REMOTE].[B].[dbo].[RemoteSP] will be called from within a stored procedure [dbo].[LocalSP] on [LOCAL].[A], which [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 [REMOTE].[B].

Adding 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.

  • When MYDOMAIN\OneUser executes LocalSP and it calls RemoteSP, are you wanting RemoteSP to merely be restricted in its ability to be called by anyone else, or in addition to the restriction, for it to also execute within the security context of MYDOMAIN\OneUser instead of GenericUser? – Solomon Rutzky Jan 16 '15 at 18:14
  • @srutzky The former. The security context within RemoteSP is not really a problem as long as only MYDOMAIN\OneUser can call RemoteSP. Note that neither GenericUser nor MYDOMAIN\OneUser has direct access to the tables, views, etc. on [REMOTE]. – Andrew Jan 16 '15 at 18:19
1

Based on comments on the question: The desire is to have RemoteSP on [REMOTE] execute within the security context of GenericUser, but only executable by [MYDOMAIN\OneUser] even though everyone accesses [REMOTE] as GenericUser via the same Linked Server with a static security setup.

That seems like an inconsistent security setup. You have a single login on [REMOTE] that you want to have two different sets of permissions for. At the very least you will have to DENY EXECUTE ON [RemoteSP] TO [GenericUser]; in order to prevent everyone outside of [MYDOMAIN\OneUser] from executing the proc over the Linked Server. But you want it to execute in the context of GenericUser so you need to add WITH EXECUTE AS 'GenericUser' to that proc. Both of these steps get you the security context with the restriction on who can run it, but so far there is no way for [MYDOMAIN\OneUser] to execute it either. And yes, it seems counter-intuitive to DENY execute to the User that is then used as the security context, but a) that be the requirements, and b) it does work as I tested it since it seemed like there was a possibility that SQL Server might give the "Did you even look at what you were trying to execute" error ;-).

Allowing only [MYDOMAIN\OneUser] to execute means creating another Login on [REMOTE] for this purpose, call it UserForRemoteSP. Be sure to GRANT EXECUTE ON [RemoteSP] TO [UserForRemoteSP];.

Another desire is that [MYDOMAIN\OneUser] still access [REMOTE] via the current Linked Server (and hence as GenericUser) for everything else besides [RemoteSP]. That can be accomplished by creating another Linked Server, call it [REMOTE_SPECIAL], that has a mapped "local login to remote login" list with only [MYDOMAIN\OneUser] defined in it, mapping to UserForRemoteSP. For the "For a login not defined in the list above, connections will:" option, select "Not be made".

Last step: in [LocalSP], in the IF SUSER_NAME block, just do:

EXEC [REMOTE_SPECIAL].[B].[dbo].[RemoteSP] @MyVar = @Id;

Nobody else will be able to use the [REMOTE_SPECIAL] Linked Server, and the only thing that [MYDOMAIN\OneUser] can use that Linked Server for is to execute RemoteSP, which will run as GenericUser due to the EXECUTE AS clause.

  • 1
    Excellent workaround. This actually, in a roundabout way, removes my reason for not wanting to create a separate linked login for [MYDOMAIN\OneUser]. I should be able just to make this login part of two different roles on [REMOTE], which will actually accomplish what I want. I'm not sure why I didn't think of that before. – Andrew Jan 16 '15 at 20:38
  • Sounds good :). I added a small bit stating an acceptance of the odd nature of the setup on [REMOTE] but that it does work ;-). And yes, if you are using Role-based security then it makes the whole task even easier / cleaner. – Solomon Rutzky Jan 16 '15 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.