Post reply

Grant only create Procedure rights

  • March 24, 2008 at 10:38 am

    #184539

    I want to grant only Create Procedure rights to a user without giving dbo to dbo schemas.

    On books online, you not only have to

    'Grant create procedure to user', but also have to 'Grant alter on Schema to the user'. In this case would be: 'Grant alter on schema::dbo to user'

    Problem is that more rights are given because of the grant alter on schema.

    Now the user can also drop tables/views and procedures as well!

    I try to Revoke the rightts to drop tables but there is no such syntax.

    Any suggestions?

  • March 25, 2008 at 11:45 am

    #793668

    Try using rolebased permissions.

    Under the major security context, add the user to the database in a dbo role. Locate the user in the database and select properties. Under securables -> add ->all objects of all types, and select databases.

    In explicit permissions you can grant or deny what you want that user to be able to do for that database.

  • March 25, 2008 at 2:48 pm

    #793765

    I dont' think that still works because when doing that under securables, you only have is: alter, control, delete, insert, references, select, take ownership...

    Nothing about Drop permissions.

  • March 25, 2008 at 2:53 pm

    #793768

    Deny would be like revoke, check that and they cannot do that function.

  • March 25, 2008 at 2:54 pm

    #793769

    Clarification... you did select Databases in the roles? within that you deny or grant

  • March 25, 2008 at 3:08 pm

    #793773

    I am also facing the similar kind of issue. I have a database which has 2 schemas. There are 2 development teams and each team reads the data from the other schema. Now the issue is that I have granted the "Alter" permission at the Schema level. As a result of this the users in a single schema can create and alter procedures in that schema. But I found out that they could also drop the tables in the same schema. If I revoke the Grant Alter access then the users cannot create/alter procedures even though they have "create procedure" permissions at the database level.

    Please advice

    Vinoj

  • March 25, 2008 at 3:18 pm

    #793776

    Lowry Kozlowski (3/25/2008)

    Clarification... you did select Databases in the roles? within that you deny or grant

    True, but as the original poster pointed out, you still need ALTER rights at the schema level. You can deny the CREATE statements (like CREATE TABLE), but the problem is that does not deny the DROP permission one gets from the ALTER rights.

    Unfortunately, I don't think there is a happy solution to this other than asking the user to create the procedure in a different schema. If ownership of the schema are the same, then ownership chaining is in place, but I don't know if that's good enough based on your need/application.

    K. Brian Kelley
    @kbriankelley

  • March 25, 2008 at 3:42 pm

    #793786

    I'm not thinking straight, I blame my sinus infection. Yeah, that's it. I only wrote that chapter in the how to cheat book. Egads.

    Use a DDL trigger. You can rollback schema changes that aren't related to procedures.

    K. Brian Kelley
    @kbriankelley

Viewing 8 posts - 1 through 7 (of 7 total)

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