Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Hybrid Permission Expand / Collapse
Author
Message
Posted Thursday, February 20, 2014 1:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
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.
Post #1543714
Posted Thursday, February 20, 2014 1:49 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 786, Visits: 691
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1543718
Posted Thursday, February 20, 2014 1:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:38 AM
Points: 12,880, Visits: 31,798
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1543720
Posted Thursday, February 20, 2014 1:58 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 786, Visits: 691
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1543724
Posted Thursday, February 20, 2014 2:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 5:45 PM
Points: 120, Visits: 676
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
Post #1543727
Posted Friday, February 21, 2014 8:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
Thank you all for the suggestions. I will consider them.
Post #1544045
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse