Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Ability to restrict user access to certain tables?

Hi,

Does MS SQL Server 2000 have the ability to limit user access to certain tables in a database?

We have a client that is connecting to our db using VPN and loading data into some tables and retrieving data from other tables.  What we want to do is restrict them from seeing any other tables in our database.

Is this possible?  If so, how?

thanks
0
bsmiley
Asked:
bsmiley
  • 2
1 Solution
 
spcmnspffCommented:
Yes, this is possible.  If you add the user to the database but add them to any database roles, the user will not be able to select from any tables by default.  Now you can control permissions at the table level by granting them select permissions on certain tables

GRANT SELECT ON Table1 TO domain\user

or if your using a sql server login for the user:

GRANT SELECT ON Table1 TO userlogin

I imagine that your current configuration is such that the user has permissions to all the tables by virtue of membership to some server/database role - i.e. db_datareader. If so, you'll have to remove the user from that role first.
0
 
spcmnspffCommented:
Sorry let try that again:


Yes, this is possible.  If you add the user to the database but DON'T add them to any database roles, the
user will not be able to select from any tables by default.  Now you can control permissions at the
table level by granting them select permissions on certain tables

GRANT SELECT ON Table1 TO domain\user

or if your using a sql server login for the user:

GRANT SELECT ON Table1 TO userlogin

I imagine that your current configuration is such that the user has permissions to all the tables by
virtue of membership to some server/database role - i.e. db_datareader. If so, you'll have to remove
the user from that role first.
0
 
bsmileyAuthor Commented:
Thanks!

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now