Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jmdaltx
Starting Member
1 Post |
 Posted - 2008-12-12 : 12:55:32
|
| I have a situation where I need to limit access to two tables that contain sensitive information. The tables will exist on two servers and in two databases within each of the two servers using the dbo schema. The access should be limited to read only for everyone except SysAdmin ideally, but we can also allow dbo access too. Initially I revoked all privileges to public for each of the two tables. I then create a role that was granted select on each of the two tables. For a test I created a SQL login with the default dbo schema and the public server role. I then added the user to the datareader role for the databases. I assumed because I had revoked all privileges from public that the user would not be able to select from the table, but they can. Is this because they are members of the datareader role and it has select permissions on the two tables? How do I limit all access to the two tables, other than to the sysadmins (and dbo if needed for the solution) and have select only granted via a specific role? By the way, we have also created an AD group that we plan to add all the select users into. Any help would be appreciated. |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-15 : 05:59:39
|
| Create a role which has DENY access to the two tables for everything except SELECT. Then add Users /Roles to this role who don't need access. |
 |
|
|
|
|
|
Table level security