• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1452
  • Last Modified:

SQL Server 2005 , How to Deny Drop Table to db_dlladmin Role

I have a a database user user1  has a database role membership in db_ddladmin
How can Deny him from  Drop objects (tables, views) , in other words he can Create objects but he cant drop it?
0
ali_alannah
Asked:
ali_alannah
  • 2
2 Solutions
 
rjbookCommented:
I think an easier solution may be to create a new database role and only grant the create table and create view permissions...I will test that quick and reply back shortly.
0
 
rjbookCommented:
Sorry, did not work the way I thought it would. I created a new Role using this syntax;

CREATE ROLE [MyRole] AUTHORIZATION [MyUser]

Then I granted the Create Table permission to the Role;

grant create table to [MyRole]

Then I added MyUser to the Role;

EXEC sp_addrolemember N'MyRole', N'MyUser'

However when I connected as the MyUser account I was able to both create a table and drop the table. I guess the create table permission must also allow the drop permission by default, bummer.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree: ddl_admin can both create and drop any tables.
a login/user that owns a table (or any other object), can also drop it.

note: a table, where a foreign key points to cannot be dropped unless the foreign key is dropped first!

note 2: as from sql 2005, you can create database/server triggers to "catch" drop table (among others) statements, and issue a ROLLBACK, so the drop cannot be performed.

I presume that is the only way you can solve that issue.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now