I have a group of 5 tables Foot_table1, Foot_table2.....Foot_table5

Now these are in the dbo schema like hundreds of other tables, but these have sensitive data and I want to deny everybody rights to these tables and leave permissions to only one "Secure_P" group.

What should be the best way to achieve this? I do not want to go through the lenghty procedure of denying each and every type of right to one user at a time, there are 50 users on this one, want to be able to do this easily...

All help appreciated...

T

 How are the user permissions assigned? through roles or just individually,

if it is through roles, you can deny permission for them by denying permission to the role.

if it is just individual users, then script them out and you can execute in one go.

or maybe you can create a role that has deny permissions on the tables and users to the role.

but this can get complicated as more things come int

From the perspective of managing permissions, the best would be to move these tables to a separate schema. Then you could make sure that only Secure_P is granted permissions on this schema. No one else are granted or denied anything.

If these tables are in dbo, and people have SELECT permission on the dbo schema, you would need to deny them permission on these tables. But if you lump them all in a group, you must make sure that the Secure_P people are not in this group, because DENY takes precedence over GRANT. On the other hand, if you enter people manually in the group that is denied permission on these tables, new users may get access to them, if you fail to add those users to that group.