Printed 2017/06/24 08:20AM

Rights for one table

By Steve Jones, 2010/09/16

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.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.