How to grant create SP but not create tables in dbo schema.

Question

How to grant create SP but not create tables in dbo schema.

chopperGuy

Right there with Babe

Points: 777
More actions
May 26, 2009 at 1:06 pm

#134588

Folks
I need some help here on SQL2K5 SP2. I should be able to grant the developers create procedure access (dbo schema) but nothing else. So far, I have not been able to achieve this. I have to add them to ddl admin in order to have them create sp in dbo schema.
Any options/solutions ?

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

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

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 1

Create a role that you're going to add your developers to. Then add ddl_admin and security_admin roles that role. Then deny CREATE TABLE to the role. That about does it. You can also get into denying them CREATE INDEX, ALTER TABLE, etc. It's a bunch of work to get the role set just right. After you get the role configured correctly, script it out & save it. You can reuse that role as needed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

chopperGuy Right there with Babe Points: 777 More actions · Answer 2

I tried that but the issue is it leaves the "drop table" priviliges behind.

Also tried this but no luck

create role Developer authorization dbo;

--grant control on schema::dbo to Developer;

revoke create table from Developer;

revoke create rule from Developer;

revoke create default from Developer;

--revoke delete on schema::dbo from Developer;

--revoke select on schema::dbo from Developer;

--revoke insert on schema::dbo from Developer;

--revoke update on schema::dbo from Developer;

revoke take ownership on schema::dbo from Developer;

Grant Create Procedure to developer;

Grant Create Function to Developer;

Grant execute on Database ::dbname to Developer;

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 3

Tell them not to drop tables. In 2000 we couldn't revoke their privileges to create indexes. We had to tell them not to do it. Although, since we built our databases from scripts in source control, it kind of didn't matter that they created their own indexes since they weren't getting into the source code anyway.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning