EXECUTE SP Permissions for child database

Question

EXECUTE SP Permissions for child database

arrjay

Hall of Fame

Points: 3455
More actions
February 19, 2015 at 7:23 am

#295431

Hi there - I was wondering if there is a way round the following issue.
I have a database which contains a stored procedure. The stored procedure contains an update script to a different database. The user group have access to the database that stores the stored procedure but do not have update rights to the database that contains the data that is being updated.
Therefore, every time the user group executes the stored procedure it fails because the security permissions do not propagate to the next database.
Is there anything I can do to get around this without grant dbo permissions to the whole user group.
Thanks!
--------------------------------------------
Laughing in the face of contention...

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i believe the best solution is to sign the procedure via ADD SIGNATURE . then the certificate permissions are used, instead of the caller, and would perform as expected.

a crappy example:

--#################################################################################################

--Make sure there is a database master key.

--database master key ALWAYS has an Id of 101

--#################################################################################################

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

BEGIN

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S3kretAcc3ss!123'

END

--#################################################################################################

--create our certificate.

--#################################################################################################

IF NOT EXISTS(SELECT *

FROM sys.certificates

WHERE name = 'MyDataSecurityCertificate')

BEGIN

CREATE CERTIFICATE MyDataSecurityCertificate WITH SUBJECT = 'My DataSecurity Certificate', EXPIRY_DATE = '12/31/2024'

PRINT 'MyDataSecurityCertificate Created'

END

ELSE

BEGIN

PRINT 'MyDataSecurityCertificate Already Exists.'

END

GO

create procedure [sp_modify_other_database]

AS

UPDATE

OtherDatabase.dbo.Provider SET UpdatedDate = getdate () WHERE ProviderID = 3

GO

-- Sign the procedure.

ADD SIGNATURE TO [sp_modify_other_database]

BY CERTIFICATE [MyDataSecurityCertificate]

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

arrjay Hall of Fame Points: 3455 More actions · Answer 2

Great! Thanks.

--------------------------------------------

Laughing in the face of contention...