Post reply

Signing a stored procedure that uses a linked server

  • March 11, 2010 at 6:00 am

    #217663

    Hi,

    For stored procedures that access several databases, I can sign the proc with a certificate and grant execute permissions on the proc and it works. Is there a way to do this for procs that use linked servers?

    Thanks.

  • March 12, 2010 at 7:03 am

    #1132655

    How you executing SP at Linked Server.?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • March 12, 2010 at 8:13 am

    #1132724

    The proc is in the format:

    USE [DB1]

    GO

    CREATE PROC dbo.usp_proc1

    AS

    SELECT a.[col1], b.[col2] FROM [table1] a

    INNER JOIN [Server2].[DB2].[dbo].[table2] b

    ON a.[ID] = b.[ID]

    GO

    Then executing it from Server1 where DB1 is located;

    EXEC dbo.usp_proc1

    What is the most secure way of granting this proc the rights to run without granting directing access on table2? I also want to avoid using a remote login on the linked server with elevated privileges, such as the sa account.

    Thanks.

  • March 15, 2010 at 12:44 am

    #1133537

    Create a new user on that DB and

    use GRANT EXECUTE on dbo.up_SP TO New_USer

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • March 15, 2010 at 1:39 am

    #1133543

    The user wouldn't have access to the other server though. How would that work?

  • March 15, 2010 at 3:18 am

    #1133562

    then create login

    and i guess you need to map that login to new server

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • March 15, 2010 at 3:42 am

    #1133569

    That would involve granting the user direct access on the remote table which I want to avoid. I just want to grant access on the stored proc. Is that possible?

  • March 16, 2010 at 8:25 am

    #1134477

    Create NewUser1 in first server.

    Create NewUser2 in remote server and grant select permissions.

    Map NewUser1 to NewUser2 in linkedserver configuration.

    In the first server create StoredProcCallLinkedServer

    as select * from server.db.sch.table

    Create also StoredProcExecuteAS

    AS

    EXECUTE AS LOGIN = 'NewUser1'

    EXEC StoredProcCallLinkedServer

    REVERT

    Then Grant impersonate on NewUser1 to the certificate and sign StoredProcExecuteAS with the certificate

    This way your UserLogin wil not have direct permission to the remote server, only NewUser1. And the UserLogin will be able to impersonate NewUser1 only when executing StoredProcExecuteAS. You need to make 2 sp because if you create only one it will not compile the first time it will be called.

    Jonathan

  • March 16, 2010 at 9:01 am

    #1134520

    Thanks for the response. That's an interesting way of doing it and I think that would achieve what I'm looking for. My only concern is that I would then need a wrapper proc for every proc and I have thousands. Is there a way of doing this without a wrapper proc? Is this documented on MSDN or anywhere else? I couldn't find anything.

  • March 16, 2010 at 9:32 am

    #1134560

    I don't know any other way, and I didn't find any documentation related to this specific situation. I played a whole day the time I needed to do this thing. You could create a proc to generate a script that will create all the needed procs.

    Jonathan

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply