Post reply

REATE/ALTER/DROP permissions on all current and new SPs & Functions of db?

  • May 9, 2011 at 4:39 pm

    #237836

    How do I grant CREATE/ALTER/DROP permissions to a user on all current and new SPs & Functions? Is

    there a role that I should be creating on each database that specifically does these things? The db_ddladmin role seems to grant too many permissions.

    What's the easiest way to do this? I'm trying to streamline the process with new databases and their respective developer.

    Thanks

  • May 9, 2011 at 7:17 pm

    #1322051

    You'll need to do the following:

    - Create a user defined role to assign permissions to.

    - Grant CREATE PROCEDURE to that role.

    - Grant CREATE FUNCTION to that role.

    - Grant ALTER on all schema in each database to that role.

    - Create a DDL trigger that checks to see if you're a member of the role and if you're not touching a stored procedure or function, it rolls back the DDL command that was issued.

    You'll need to do that for every DB you wish to grant such access in.

    K. Brian Kelley
    @kbriankelley

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

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