Linked Tables and SQL Server permissions

[Bill]

I manage a SQL 2005 database that has different groups of users using
Access to maintain their data. Each of these groups of people have
access to a different collection of tables which I implemented using
database roles (ex: Group A has Role A which gives access to tables
X, Y and Z). The problem arises when a person attempts to link to the
tables in the sql database for the first time. The link table process
via ODBC does not show any tables. The workaround I have is to
elevate their database permissions to db_datareader which lets them
link to the tables.

What I am trying to figure out is what objects, besides the tables in
their respective role, do these folk need access to to be able to see
the sql server tables?

Bill

 

[Jeff Boyce]

Bill

How are they trying to link via ODBC?

Are they using their Windows logon or SQL-Server authentication? Are you?

Does the ODBC 'connector'/DSN use the Group's Role's logon or their own
personal logon?

Just a couple thoughts...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Sylvain Lafontaine]

Here's a good introduction on Grant View Definition:
http://www.mssqltips.com/tip.asp?tip=1593

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bill]

Thanks for the help, it solved my problem.