Allowing a user to view/edit only a specific table in a database with ~50 tables

Posted on

Question :

The database I’m working with has about 50 tables in it.

A single user requires edit permissions on one of the tables, but should not have access to any other tables.

I know how to deny her the ability to edit any of the tables:
properties > permissions then denying her all the options.

Using this method, she can still view the tables.

Is there a way I can make it so she can only view the one table?

Answer :

It’ll involve 1) removing the user from any database fixed roles like db_datareader (only public will remain), 2) creating a custom role in the DB, 3) granting the new role specific permissions on the one table, and 4) adding the user as a member of the new role. You’ll also want to make sure public has no grants to user objects as all users in a DB are a part of the public role.

Leave a Reply

Your email address will not be published. Required fields are marked *