Permissions Question

[wilbiew]

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?

[rmiao]

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.

[wilbiew]

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!

[rmiao]

If you don't put user in db_owner role, the user can't drop non-owned tables.