Is there a built in Role which I can grant to a user that allows them to do anything with the schema and the data, but not security and maintenance?

for example,

i know data_writer can add/update/delete, but iirc it doesnt allow create table

I want to be able to perform CRUD on

  • tables, views, sprocs
  • indexes, FKs etc
  • data inside tables
  • full text catalogs

but not allow:

  • dropping the db
  • messing with the security settings
  • messing with the backup/maintenance plans
  • anything else that isnt related to the schema or data in it

Is this easily achieved?

Do I need to create a custom role for this? If so, what permissions do I need to assign it?


  • Personally I would do a custom role, as it provides you with the best options – Scoregraphic Sep 3 '10 at 11:32
  • How do i do that? the new role window isnt very intuitive.. – Andrew Bullock Sep 3 '10 at 11:50
  • you only seem to be able to set permissions for existing objects, which doesnt help me with create table – Andrew Bullock Sep 3 '10 at 11:52

I believe db_ddladmin is intended for that purpose. It won't give them the ability to CRUD data, so they will also need db_datawriter and db_datareader as well. That being said, I'm not 100% sure that it will preclude the user from dropping the database, so you may want to test that one.
If that does not meet your needs, then you will need to define your own role. Under the Security node of the database, you want to create a new database role. You can then add a Securable for the database, which will contain many of the permissions you're looking for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.