Can I restrict members of db_datareader from SELECTing from a tabl

I have a large database with lost of users, many of whom are in the
db_datareader role.
I have a table that I want to restrict SELECTs on to just sa's.
How can I restrict members of db_datareader from SELECTing from this table?
As shown below, I cannot REVOKE or DENY SELECT from this role.
I could remove everyone from db_datareader and put them in a special role
with SELECT on everything but this table. But is there an easier way?
Thanks
USE admin
CREATE TABLE myTable
(col1 int PRIMARY KEY IDENTITY
,col2 int
,col3 int)
INSERT myTable (col2) VALUES (10)
INSERT myTable (col2) VALUES (20)
INSERT myTable (col2) VALUES (30)
SELECT * FROM myTable
execute as login = 'appuser'
select suser_name()
SELECT * FROM myTable
revert
sp_helprolemember db_datareader
REVOKE SELECT ON myTable TO db_datareader
--Cannot grant, deny or revoke permissions to or from special roles.
DENY SELECT ON myTable TO db_datareader
Hello,
You can not attempt to issue a DENY or REVOKE on to a fixed role.Issue the
DENY SELECT statement on to all uses who are not supposed to
query the table and see if that works well.
THanks
Hari
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:B4980135-A8AE-4D4B-8B11-2623407D8986@.microsoft.com...
>I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
> How can I restrict members of db_datareader from SELECTing from this
> table?
> As shown below, I cannot REVOKE or DENY SELECT from this role.
> I could remove everyone from db_datareader and put them in a special role
> with SELECT on everything but this table. But is there an easier way?
> Thanks
> --
> USE admin
> CREATE TABLE myTable
> (col1 int PRIMARY KEY IDENTITY
> ,col2 int
> ,col3 int)
> INSERT myTable (col2) VALUES (10)
> INSERT myTable (col2) VALUES (20)
> INSERT myTable (col2) VALUES (30)
> SELECT * FROM myTable
> execute as login = 'appuser'
> select suser_name()
> SELECT * FROM myTable
> revert
> sp_helprolemember db_datareader
> REVOKE SELECT ON myTable TO db_datareader
> --Cannot grant, deny or revoke permissions to or from special roles.
> DENY SELECT ON myTable TO db_datareader
>
>
|||Hi Dave
"Dave" wrote:

> I have a large database with lost of users, many of whom are in the
> db_datareader role.
> I have a table that I want to restrict SELECTs on to just sa's.
>
It sounds like you need to make your security more granular, by creating
your own role(s) that only have the permissions the users actually need. You
could also do this using Windows Groups (or a combination of both!) if you
are using Windows Authentication. By only using Stored Procedures to access
your data it may be possible to limit who can SELECT from a table further.
John