Question on object permissions by proxy

[jsteph]

Hi,
I have a general question about sql-server (2005) permission granting. Is it part of the security model to create a stored procedure as a user with higher permissions and grant Execute permission on that sp to a user with lower permissions--ie, those without access to the objects referenced in that procedure--and have it run without "access denied" errors.

For example, if UserA has permission to select and update TableA, I want to write a procedure that updates a single field in TableA and returns selected records from that table. Now I want a few other users who have no rights on that table--say UserB and UserC--to be able to run that procedure, but I don't want to get into the morass of assigning column-level permissions to the other users.

Is it possible to do this? I'm essentially asking for something akin to the "run as" in Windows, or the "With Owner Permissions" capability in Microsoft Access.
--Jim

 

[mrdenny]

***This assumes that you are not using dynamic SQL.***

You don't need to do anything with permissions at the table level. When you create a procedure and grant a user the right to run the procedure, the permissions chaining allows the user to do what ever INSERT/UPDATE/DELETE/SELECT commands exist within the procedure via the execution of the procedure only.

In other words grant the user the right to execute the procedure are you are done.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog