SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by Jason Brimhall on 16 September 2010

Simple, accurate and direct.  That is the way I prefer to grant permissions.

Posted by Robert L Davis on 17 September 2010

I wouldn't advocate for this approach if 1 user needs access to 1 table. I would use a role only if multiple users needed access to a single table.

I definitely agree with the comments on the groups. We have 3 groups for members of our engineering team, one for dev, one for test, and one for the PM's. I see a lot of applications that simply have a single group for everyone.

Posted by Steve Jones on 17 September 2010

I always use a role, even for one person. People are transient. If you grant it for one, typically you'll grant it for another as well, or they'll move on and the job moves to a new person.

Leave a Comment

Please register or log in to leave a comment.