Post reply

Hybrid Permission

  • February 20, 2014 at 1:40 pm

    #304614

    Hello,

    I have a couple of databases where I need to give a couple of developers special permissions. What we need to do is give the users permissions to create/alter tables, but not create/allter Procedures, views, functions, types. I cant seem to separate the two sets. Does anyone have an idea?

    Thanks for your help.

  • February 20, 2014 at 1:49 pm

    #1690883

    I think the only reasonable way to achieve this is to create a schema for this purpose, and then grant the users CREATE TABLE permission on database level and ALTER permission on that schema.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • February 20, 2014 at 1:54 pm

    #1690885

    I don't believe the permissions are that granular; as i understand it, once you grant ALTER, that's ALTER any object.

    I believe you can only do by granting ALTER, and then restricting events via a DDL trigger in each database; i posted a complete solution in another post for a similar issue, where someone wanted to restrict a group to create/alter views, so the solution would be easily modified for your issue.

    see if this thread helps:

    http://www.sqlservercentral.com/Forums/Topic1241211-391-1.aspx

    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!

  • February 20, 2014 at 1:58 pm

    #1690889

    Lowell (2/20/2014)

    I don't believe the permissions are that granular; as i understand it, once you grant ALTER, that's ALTER any object.

    That is correct. If you have ALTER on a schema, that implies ALTER on all objects on that schema. This can be counter-acted with DENY but that is of course hopeless.

    That's why you can't have these user-defined tables in dbo, but as I suggested in a separate schema. The users will have permission to alter anything in that schema, but they will not have permissions to create anything but tables, so there will not be anything else do alter than then tables.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • February 20, 2014 at 2:03 pm

    #1690892

    I agree with erland, do a new schema for devs, that's the easiest way to manage it, when you add new devs you can just add them to the same schema

  • February 21, 2014 at 8:43 am

    #1691160

    Thank you all for the suggestions. I will consider them.

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

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