Grant create proc permission to a user-defined role

  • First off, a Happy New Year 2013 to all. Maybe I'm suffering from the after-effects of last night but I can't figure out what permissions I need to grant to do this right...

    We are a small shop and generally don't create procs, we don't do development work, all of our applications are purchased. But one of the report developers wants to try using a proc for a report.

    What I think I want to do is create a schema in the database and allow the members of the developer role to create, delete, alter, view definition and execute procs in their schema.

    So I created the schema, created the db_role and added users to the db_role.

    USE [master]

    GO

    CREATE LOGIN [Heyen] WITH PASSWORD=N'normanheyen', DEFAULT_DATABASE=[AdventureWorks2008R2], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    USE [AdventureWorks2008R2]

    GO

    CREATE SCHEMA [Test] AUTHORIZATION [dbo]

    GO

    CREATE USER [Heyen] FOR LOGIN [Heyen] WITH DEFAULT_SCHEMA=[Test]

    GO

    CREATE ROLE [Developers] AUTHORIZATION [dbo]

    GO

    ALTER AUTHORIZATION ON SCHEMA::[Test] TO [Developers]

    GO

    EXEC sp_addrolemember N'Developers', N'Heyen'

    GO

    EXEC sp_addrolemember N'db_datareader', N'Developers'

    GO

    GRANT ALTER ON SCHEMA::[Test] TO [Developers]

    GO

    GRANT CONTROL ON SCHEMA::[Test] TO [Developers]

    GO

    GRANT DELETE ON SCHEMA::[Test] TO [Developers]

    GO

    GRANT EXECUTE ON SCHEMA::[Test] TO [Developers]

    GO

    GRANT SELECT ON SCHEMA::[Test] TO [Developers]

    GO

    GRANT TAKE OWNERSHIP ON SCHEMA::[Test] TO [Developers]

    GO

    GRANT UPDATE ON SCHEMA::[Test] TO [Developers]

    GO

    GRANT VIEW DEFINITION ON SCHEMA::[Test] TO [Developers]

    GO

    GRANT CREATE PROCEDURE ON SCHEMA::[Test] TO [Developers]

    GO

    When I run this as SA, it reports that:

    Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

    And the final command (GRANT CREATE PROCEDURE) gives a syntax error message.

    And of course, the user cannot create a proc in the database, much less not in the Test schema.

    So, what should I be doing to get this to work. It seems like it should be fairly common but it isn't exactly clear to me how to get it to work.

    Thanks to all,

    Norman

  • This statement made the Developers role the owner of the Test schema. In being the owner, any grants you attempted to do that overlapped the permissions afforded by schema ownership would fail:

    ALTER AUTHORIZATION ON SCHEMA::[Test] TO [Developers]

    As a side note, CREATE TABLE and CREATE PROC are database-level permissions and therefore cannot be granted at the SCHEMA level. The CRAETE TABLE plus ALTER SCHEMA privileges are required to create tables in a particular schema, although schema-ownership supercedes the need for the explicit ALTER SCHEMA permission.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • So the ALTER AUTHORIZATION statement made the [Developers] the owners of the schema? Then I don't really need to add the rest of the GRANT statements since they are now the owners of their schema.

    Apparently I understand less about security than I thought...

    Thanks! I appreciate the explanation.

    Norman

  • n.heyen (1/1/2013)


    So the ALTER AUTHORIZATION statement made the [Developers] the owners of the schema?

    Exactly.

    Then I don't really need to add the rest of the GRANT statements since they are now the owners of their schema.

    Almost. You will still need to grant to the Developers role the CREATE PROC permission which is at the database level not the schema level:

    GRANT CREATE PROCEDURE TO [Developers]

    GO

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thank you, I did figure that missing step out a bit ago. It seems to work as near as I can tell from some limited testing.

    I appreciate the advice and help, hopefully if I play with this enough, it will make more sense...

    Norman

Viewing 5 posts - 1 through 4 (of 4 total)

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