Rights for one table

I ran across a thread that was asking how to grant rights to a person for one table only, and not other tables.

My response is simple:

CREATE ROLE MySingleTableRole
dbo.MyCustomers TO MySingleTableRole
sp_addrolemember 'MySingleTableRole', 'Steve'

That’s it. By default users do not have rights to any tables even if they have rights to the database. If you have a user that needs rights to one table, just grant them rights to that table.

If the user is a member of a group that has rights to other tables, you should probably remove them from the other group/role. Then build another group for them, or for the other users. If this is the case, then your group is incorrectly being used as you have people in the group needing different permissions.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.