Access to a table

Question

Access to a table

hoseam

SSCertifiable

Points: 5533
More actions
March 10, 2014 at 2:11 am

#286555

Hi
I have a database and table dbo.Test_Table, with list of Users under Security -> Users. those users have access to all the tables in my database.
I would like to know how do I assign certain users to specific tables (e.g dbo.Test_Table) within the database, that only those Users can view those tables within my database.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

First step: make sure all those users (except admins of course), can't see any table. None at all.

Then create different database roles, add the correct users to a role and give the roles appropriate SELECT permissions on the tables (and maybe VIEW DEFINITION if needed, I don't know if this privilige is implied by SELECT).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

hoseam SSCertifiable Points: 5533 More actions · Answer 2

Database already exist, with those users, this logic has to be implemented on already existing database and tables .

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

hoseam (3/10/2014)
Database already exist, with those users, this logic has to be implemented on already existing database and tables .

Your point being? Permissions can be given and be taken away at any point in time. You don't have to change the database or the tables.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

What you're looking to do is use the GRANT command to give people access to a particular table. There's an example at the link. You can combine that with the REVOKE command to remove existing permissions or the DENY command to prevent people from access things. There are examples at each of the links showing how to do what you're after.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning