May 4, 2005 at 11:54 am
Slightly confused in permission hierarchy. Let say user “Yuri’’ was assigned to db_datawriter role and has permission to execute stored procedure (SP) usp_Test. This SP drops and creates table (index), i.e. needs db_ddladmin role permissions. Does user “Yuri” have enough rights to execute it? Thanks
May 4, 2005 at 1:07 pm
If it is a standard table then no. But if it is a temporary table prefixed with eiher # or ## then yes.
May 4, 2005 at 1:18 pm
Thanks. Actually you answered already my second question I was about to ask regarding temporary table related permissions.
And just to clarify regarding standard tables- “EXEC” permission allows User only to start stored procedure but to complete it User must have permissions for everything what this SP is doing. Correct? Thanks
May 5, 2005 at 11:09 am
No. The code can do Select, Update, Delete and Insert against tables and views and EXEC against other SPs or utilize user defined functions without granting permissions. That is unless you try to do something with dynamic sql in the SP.
Ex.
DECLARE @sql varchar(800)
SET @sql = 'select * from X'
EXEC (@sql)
Then because the execution takes place outside the scope of the SP you will have to grant rights to the given table(s),view(s) or whatever else may be needed. Best to avoid if you can.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply