Post reply

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

  • 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 ?

  • May 26, 2009 at 1:28 pm

    #1000141

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • May 26, 2009 at 2:08 pm

    #1000178

    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;

  • May 27, 2009 at 5:33 am

    #1000486

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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