Post reply

Permission hierarchy

  • May 4, 2005 at 11:54 am

    #87412

    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

    #556892

    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

    #556896

    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

    #557170

    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