Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

permissions for stored proc that reads distributed partitioned view Expand / Collapse
Author
Message
Posted Thursday, January 30, 2014 11:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 07, 2014 12:47 PM
Points: 40, Visits: 29
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?
Post #1536500
Posted Friday, January 31, 2014 8:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:42 PM
Points: 12,738, Visits: 31,040
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1536819
Posted Friday, January 31, 2014 9:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 07, 2014 12:47 PM
Points: 40, Visits: 29
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.
Post #1536827
Posted Friday, January 31, 2014 9:06 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:42 PM
Points: 12,738, Visits: 31,040
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1536830
Posted Monday, February 03, 2014 6:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 07, 2014 12:47 PM
Points: 40, Visits: 29
Thank you!
Post #1537291
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse