permissions for stored proc that reads distributed partitioned view

Question

permissions for stored proc that reads distributed partitioned view

evansrf

SSC-Addicted

Points: 419
More actions
January 30, 2014 at 11:43 am

#282906

I'm having trouble understanding the requirements for this.
I'd like to have a stored proc that selects/updates a distributed partitioned view.
I really don't want to have to grant rights to the underlying tables, just execute rights on the stored proc to the user (SQL Server login used by a web app).
How is this accomplished?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

evansrf (1/30/2014)
I'm having trouble understanding the requirements for this.
I'd like to have a stored proc that selects/updates a distributed partitioned view.
I really don't want to have to grant rights to the underlying tables, just execute rights on the stored proc to the user (SQL Server login used by a web app).
How is this accomplished?

automatically with no extra effort, but there's stuff you should know.

this is a very common scenario, where someone gets access to a procedure, but not underlying data itself.

i've had databases where the app was only granted execute on specific procs, and never the underlying objects.

if a user is granted execute on a stored proc, the work will be performed as expected, even though the calling user does not have access to the underlying objects; this is by design, but it has a caveat/assumption:

as long as all the objects touched by dbo.yourprocedure are in the same database, and under the same schema (dbo.) then the work is performed as if the owner executed the procedure due to ownership chaining.

it only gets complicated when you refer to other schemas, databases, linked servers, etc.

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!

evansrf SSC-Addicted Points: 419 More actions · Answer 2

Thank you for the reply.

The issue is that the underlying tables are indeed on separate instances.

The distributed partitioned view references tables on two servers (will be 4 in production).

Accessing the view directly doesn't seem to be an issue, but since the app will be public facing, we prefer to have only stored procs available to the application user account.

Having some difficulty making it work without granting permissions to the underlying view/tables.

Lowell SSC Guru Points: 323518 More actions · Answer 3

ok in that case, what you want is a certificate signed stored procedure.

then the credentials of the certificate are used, and that can satisfy the cross database/instance permissions issue, i believe.

this was my first hit for "sql server certificate signed stored procedure"

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

This is designed for scenarios when permissions cannot be inherited through ownership ...

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!

evansrf SSC-Addicted Points: 419 More actions · Answer 4

evansrf

SSC-Addicted

Points: 419

February 3, 2014 at 6:51 am

#1685463

Thank you!