Post reply

Prevent table alteration while allowing procedure alteration

  • December 13, 2006 at 6:38 am

    #122066

    Hi SQL gang

    I have a development database in SQL 2000 on which i need to prevent users from altering the tables but i want to allow them the right to alter procedures.If i simply remove them from the db_ddladmin role then i also revoke their right to alter the procedures.

    Any suggestions

     

    thanks

     

  • December 14, 2006 at 6:38 am

    #678055

    One approach for this is to have the procedures owned by a datbase role. The role is given explicit permissions to create stored procedures. The users are then given permissions to be a member of the role. This way they can create or alter any stored procedure owned by the role. Once the procedure is moved to your production serever the users are just taken out of the role. The role must exist in the production environment. You also have to call the procedure by the fully qualified name.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply