Grant only create Procedure rights

Question

Grant only create Procedure rights

Hollyz

SSCommitted

Points: 1763
More actions
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?

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

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

Lowry Kozlowski SSCommitted Points: 1918 More actions · Answer 1

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.

Hollyz SSCommitted Points: 1763 More actions · Answer 2

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.

Lowry Kozlowski SSCommitted Points: 1918 More actions · Answer 3

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

Lowry Kozlowski SSCommitted Points: 1918 More actions · Answer 4

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

The Wisenut Right there with Babe Points: 756 More actions · Answer 5

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

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