Signing a stored procedure that uses a linked server

Question

Post reply

Signing a stored procedure that uses a linked server

JWOL

Hall of Fame

Points: 3471
More actions
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.

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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;-)

JWOL Hall of Fame Points: 3471 More actions · Answer 2

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.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

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;-)

JWOL Hall of Fame Points: 3471 More actions · Answer 4

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

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;-)

JWOL Hall of Fame Points: 3471 More actions · Answer 6

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?

jonathan.lavoie Grasshopper Points: 14 More actions · Answer 7

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

JWOL Hall of Fame Points: 3471 More actions · Answer 8

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.

jonathan.lavoie Grasshopper Points: 14 More actions · Answer 9

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