how to create database role to assign privilidge read and write for few tables

Question

i need to assign read and write permission to few tables on database,

how to create database role to assign this privilidge and assign that role to account,

please assist,

Answer 1

This is all covered in the documentation that you can read. I recommend you start there.

At a very high level.

1. Create the database role.
2. On the Properties for the role go to the Securables section.
3. Select the objects (tables, sprocs, etc) that you want to grant access to (or deny as the case may be).
4. Select the permissions for the object (select, insert, etc).
5. Apply the changes.
6. Add users to the role. Note that a user may be in multiple roles so ensure that you don't add a user to a role that has more permissions then the locked down role otherwise you defeated the purpose of doing that.

To be honest, in my opinion, securing users at this fine grain a level is going to be hard to manage. It's almost like giving users specific access to only some files in a folder structure. You might do better to:

• Give them only db_datareader role if they shouldn't be modifying any data.
• Give them only exec sproc rights and wrap all DB access in the sprocs
• Don't give them any access to DB and create a wrapper API that they can call to get the data based upon permissions in the API.