http://www.sqlservercentral.com/blogs/steve_jones/2010/09/16/rights-for-one-table/

Printed 2014/10/01 10:31PM

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
GO
GRANT SELECT ON
dbo.MyCustomers TO MySingleTableRole
GO
EXEC
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-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.