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.