Security – Need a secure way of granting access to xp_cmdshell

There are two major problems that stood out for me:

1. Your execute statement is sending the literal strings (e.g. '@val1') within the query to Instance2 because they're all contained within a string. In order to send the values, you would need to change that part of the execute statement to read:

   Execute ('Update C set col1 = ' + @val1 + ', col2 = ' + @val2
            + ' where col2 = ' + @ID) as user1 at Instance2
   

   Note that the code above assumes both @val1 and @val2 are types which do not need to have quotes around them, otherwise you'd use the following:

   Execute ('Update C set col1 = ''' + @val1 + ''', col2 = ''' + @val2
            + ''' where col2 = ''' + @ID + ''') as user1 at Instance2
   

2. Your query is not benefiting from any potential preoptimization on Instance2. Therefore, I would suggest creating a stored procedure on Instance2 so that you could benefit from pre-runtime optimization of the query, and also use all the optimization hints that you might want to place in the query. So on Instance2, you could create a procedure like this (note again that I have assumed integer datatypes):

   CREATE PROCEDURE user1.UpdateC (@val1 int, @val2 int, @ID int) AS
   BEGIN
       UPDATE C WITH (ROWLOCK) SET col1 = @val1, col2 = @val2 WHERE col2 = @ID
   END
   

   Then, your local script could replace the corrected code in part 1 above with the following (again, assuming integers):

   EXECUTE ('EXECUTE user1.UpdateC ' + @val1 + ', ' + @val2 + ', ' + @ID)
       AS user1 AT Instance2
   

See this article:

http://technet.microsoft.com/en-us/library/cc293611.aspx