Stored Procedure question

fender80

Hello everyone,

I have a question on running a stored procedure:

If you have a user running a stored procedure on a table on schema .dbo and the stored procedure will not run with an error of user not having "x" permission on the particular table:

Do you give the specific user whatever permissions are needed to perform actions on the table or run the stored procedure as another user with proper permissions? Would you ever run as .dbo (would that even work?)?

This is a paraphrased question I've seen on a practice exam and cannot seem to find an explanation for the answer. Many thanks!

RobertKaucher

If you are forcing CRUD ops to be done via a stored procedure you would probably not want to grant permissions directly on the table for anything other than read. Here is an article that will help:

Granting permission with the EXECUTE AS command in SQL Server 2005