Secure a specific table

Question

Secure a specific table

Peter Gadsby

SSCrazy

Points: 2718
More actions
May 28, 2010 at 8:40 am

#233149

Hi,
I have a database that has a number of different tables within it. But one specific table has very sensitive data in it, so I only want specific users to see the data within it.
Can you tell me how I set permission to revoke all permissions (including sysadmins) from the table so that only a specific user can see or update the data.
I am using mixed security mode.
Thanks
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk

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

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

jvanderberg SSCertifiable Points: 6762 More actions · Answer 1

The best way would be to put it in another schema and manage the security on that schema, but I will operating under the assumption that you can't move it to a new schema.

Create a database role and add users to it. Grant that role access to the table, and no other roles or users should have access.

This will not prevent administrators from accessing this table. The nature of the roles means that it has to have access to everything. If you really need to protect the data from administrators, encryption is probably the only way (presumably where the responsible parties have the encryption key, and the administrators do not). If the data is truly that sensitive, encryption might be a good idea any way.

--J

Peter Gadsby SSCrazy Points: 2718 More actions · Answer 2

Ok thanks. I'll give encryption a crack... The problem is this is HR data and therefore even administrators should not see the information.

Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk

Peter Gadsby SSCrazy Points: 2718 More actions · Answer 3

If I added a schema, could I restrict sysadmins to that?

Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk

jvanderberg SSCertifiable Points: 6762 More actions · Answer 4

No, you can't restrict sysadmin access from anything. They are the sysadmin, and have to be able to maintain the entire database. Using a schema is just best practice, and allows you to group multiple HR tables together without having to maintain their security independantly. It's a little more scalable than securing a single table.

--J