1

In our database we have about 20 users, that is used be some program (and users are added with this program). Now there was new requirement to use 2 database and do not allow delete data from one database.

What would be easiest way to deny insert/delete access for some tables (not all)? All users have db_datareader, db_datawriter.

Improve this question

2 Answers 2

Reset to default
3

It would be confusing if you granted someone db_datawriter and then denied them delete rights. So if you can, remove them from the db_datawriter and then grant them the rights they need:

create role role_MyApp
grant select, insert on Table1 to role_MyApp
...
exec sp_adduserrole 'role_MyApp, 'User1'
Improve this answer
2
  • With new role it probably will be easier to administrate users, than modify rights for each. Probably will do that and especially thanks for code :)
    – Dainius
    Commented Feb 5, 2012 at 12:02
  • Nice example with the application roles.
    – StanleyJohns
    Commented Feb 6, 2012 at 16:07
0

Deny all modifying operations to public db role or if it is possible - set the DB into read-only mode

or

place tables that shoupd be unmodified onto read-only filegroup

Improve this answer
2
  • I set "insert" permission on table for database role [public] to "deny". But it's still possible to insert new record in that table. User is only member of "db_datareader", "db_datawriter". There's few tables, that user should be ablo to modify, so it's not possible to set read-only mode.
    – Dainius
    Commented Feb 5, 2012 at 11:59
  • Try to avoid mixing various paradoxial permissions involving public role. And - updated answer
    – Oleg Dok
    Commented Feb 5, 2012 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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