Allowing a user access to only a few tables

[Dustin]

With MS SQL 2000 Enterprise Manager, is there a way to allow a user access
to only a few tables, but deny the user access to the rest without having to
go to all of the tables and denying access? The database has roughly 50
tables, but only 3 should be granted to the new user, so as you can see it
would be a painstaking task to manually do this with the *cough* mouse. Or,
if I can run some sort of grant script, that would work too. Thank you!

[Andy S.]

SELECT 'DENY ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO <username>'
FROM SYSOBJECTS ORDER BY NAME

SELECT 'GRANT ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO
<username>' FROM SYSOBJECTS ORDER BY NAME

Highlight the results you want and run. You can alternate <username>
out for public or a specific group name. Good luck.

****************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
an********@NOMORESPAM.yahoo.com

Please remove NOMORESPAM before replying.

This posting is provided "as is" with
no warranties and confers no rights.

****************************************

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dustin]

"Andy S." <an********@NOMORESPAM.yahoo.com> wrote in message
news:40*********************@news.frii.net...

SELECT 'DENY ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO <username>'
FROM SYSOBJECTS ORDER BY NAME

SELECT 'GRANT ALL|SELECT|INSERT|UPDATE|DELETE ON '+name+ ' TO
<username>' FROM SYSOBJECTS ORDER BY NAME

Highlight the results you want and run. You can alternate <username>
out for public or a specific group name. Good luck.

****************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
an********@NOMORESPAM.yahoo.com

Please remove NOMORESPAM before replying.

This posting is provided "as is" with
no warranties and confers no rights.

****************************************

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hello Andy,

Thank you for the help. I've run that script, and in the Expr1 column the
first line is:
DENY ALL|SELECT|INSERT|UPDATE|DELETE ON Aliases TO vms

So I have selected the line and selected "run" from the list. Is this the
propper way of denying the user "vms" to the aliases table? The reason I ask
is because when I check the permissions on that table, the user still has
all options (select, insert, etc.) checked. Again, thank you for the help!