October 24, 2008 at 2:29 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 8:08 am
hello there,
i am not 100% sure in my answer so if its incorrect my apologies BUT i do believe if you are the owner of an object(meaning able to create the tables..sp..etc) then you are defaulted to have the ability to drop them as well.
Good Luck
-D-
DHeath
October 24, 2008 at 8:13 am
There was a post very similar to what you are trying to accomplish. Here is the link http://www.sqlservercentral.com/Forums/Topic581296-359-1.aspx
-Chris
October 24, 2008 at 9:01 am
pprajapati (10/24/2008)
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..
Unlike create permissions, you can't grant permission on alter statement without specifying the object. If you want a user to be able to alter an object, you need to grant the permission on the object it self. For example:
grant alter on MyTable to SomeUser
One option that you have is to grant alter permissions on each view, table, procedure and user defined function in the database. When ever you’ll create a new object, you’ll need to grant the alter permission for the new object as well. Another option that you might consider is to join the user to the db_ddladmin role and create a DDL trigger. In the DDL trigger you can check if the statement is a drop statement and if you want the user to be able to drop the object. If you don’t want it, you can rollback the drop statement. One word of caution - I never tried this approach and it is a risky approach.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply