Is there a way to create a SQL login that has permission to read/write data, create views, stored procedures, and user defined functions, but does not have permission to drop and create tables?
Printable View
Is there a way to create a SQL login that has permission to read/write data, create views, stored procedures, and user defined functions, but does not have permission to drop and create tables?
You can put the user in db_datareader, db_datawriter and db_ddladmin roles. Can deny create table permission in sql2k5, but can't do that in sql2k.
Thanks for the response. I've tried your suggestion, but it seems to work only for denying the right to create tables. I do not find a way to deny dropping tables.
The other alternative that I see is giving read/write access (db_datareader, db_datawriter) to the SQL login while giving the login rights to create stored procedures, views, and functions
grant create function TO [login]
grant create procedure TO [login]
grant create view TO [login]
Please let me know if there is a better way. Thanks again!
If you don't put user in db_owner role, the user can't drop non-owned tables.