October 24, 2008 at 1:49 am
I have created one login 'test' . give the database role db_datareader, datawriter, db_ddladmin on perticular database.
I want user is able to create/alter table , view and store procedure only,
user can not to drop table/view/procedure.
so i want deny drop permission to user.
I have tried ...
use database
go
deny drop to user..
but it raise errro like ...incorrect syntax near drop..
is there any idea?
pls help me..
October 24, 2008 at 2:25 am
You cannot deny (or grant) Drop permissions only.
In order to be able to drop a object you need to have at least ALTER permission on the object. Unfortunately for your case, if you deny ALTER, you also deny them to change the tables or views.
The best option I see would be a DDL trigger which would rollback any DROP commands, but allows CREATE and ALTER.
[font="Verdana"]Markus Bohse[/font]
December 10, 2008 at 2:12 pm
Well, deny ALTER doesn't work either.
The Security in SQL Server is completely wacked (i'd use other superlatives, but I don't think they'd be appreciated).
When Adding User/Role permissions to a Database you can explicitly declare the permission for "Create Table". However, in the Schema Permissions you only have "alter", or "Control". If you don't allow the "Atler" to the dbo schema, setting the "Create table" on the database does nothing, because the dbo schema permissions denies the create table command. When you GRANT ALTER privileges on the DBO schema, it allows you to create a table, but even when I have specifically added all DBO objects to my Database Role and Selected "Deny" on the Alter privileges for my static objects, I can still drop them.
I've even set this all up, and then opened the permissions on the table directly and verified the "Effective Permissions" for a user, and for those tables the were reported as Delete, Insert, Select, Update. But I could still execute the drop command.
So, frankly, sql server doesn't know how to write security to work accordingly. typically, deny is always stronger than grant, but that is not the case (no matter what the white papers say, the experience overrides their theory.)
Frankly, I don't want to allow ALTER or DROP, but I do need to allow the users to Create/Drop tables on the fly...just specific tables are supposed to be static...not drop-able.
*shrug*
Jaeden "Sifo Dyas" al'Raec Ruiner
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply