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