By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,705 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,705 IT Pros & Developers. It's quick & easy.

Allowing a user access to only a few tables

P: n/a
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!
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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!
Jul 20 '05 #2

P: n/a
"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!
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.