Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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'
share|improve this answer
    
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 Feb 5 '12 at 12:02
    
Nice example with the application roles. –  StanleyJohns Feb 6 '12 at 16:07

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

share|improve this answer
    
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 Feb 5 '12 at 11:59
    
Try to avoid mixing various paradoxial permissions involving public role. And - updated answer –  Oleg Dok Feb 5 '12 at 12:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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