Post reply

Create and Execute permissions for stored procedures

  • July 20, 2006 at 7:42 pm

    #75020

    What are the permissions to be given for a user  to both create and execute stored procedures in a database?

    I think only db_owner can do both the tasks. or is there any other secured permission other than db_owner?

     

  • July 20, 2006 at 8:43 pm

    #650582

    granting someone the role ddl_admin allows them to create procedures i believe, and by default anything they create they can execute.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 24, 2006 at 11:56 pm

    #651255

    Is this for a development or production database?

    A simple GRANT CREATE PROCEDURE TO JOEUSER is sufficient for the user to create and execute procedures under their own schema. Anything they OWN, they can run.

    If they must create the objects under dbo, then the options are db_ddladmin + db_securityadmin, or db_owner.

    Of the "dbo" options, I prefer the former. Users can create procs, but must grant execute to an appropriate role that they also belong to in order to execute them. It promotes use of a role based model, and forces the developers to at least consider/acknowledge the security of their code objects.

    As for using db_owner, I feel it promotes programming laziness in the developers. Security is out of sight & out of mind.

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

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