Sql-server – How to deny drop, delete permission for a table

permissionssql-server-2008

I want to give Create , alter , read permission on object to user but not insert , update ,delete,drop permission to user , How can i do it in sql server 2008?

Best Answer

To CREATE a table, stored procedure or function in SQL Server you need to GRANT the following 

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO [someuser/somerole]

to a user or role. I added VIEW in since you probably wanted that too.

You also need to 

GRANT ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::schemaname TO [someuser/somerole]

to that user so that they can modify or use what they created.

With ALTER however that user would have the ability to DROP and ALTER anything in the schema which is not what you want based on your question.

ALTER PERMISSION SQL SERVER 2008

ALTER Confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope. For example, ALTER permission on a schema includes the ability to create, alter, and drop objects from the schema.

You could restrict that user to their own schema but that would probably create a lot of work for you since any application using your database would have to be aware of that new schema.

Outside of permissions, another way I know of to prevent dropping objects from a database is with a database trigger. Something like this: How to deny drop permission for a table? 

CREATE TRIGGER NO_DROP_TABLE
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'Dropping tables are not allowed'
ROLLBACK
Related Question