| Computers Forum Index » Computer - Databases - MS Access » Linked Tables and SQL Server permissions... |
|
Page 1 of 1 |
|
| Author |
Message |
| Bill... |
 Posted: Fri Oct 23, 2009 3:07 pm |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
| Jeff Boyce... |
| Posted: Fri Oct 23, 2009 7:34 pm |
|
|
|
Guest
|
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.
"Bill" <bill.parrott at (no spam) gmail.com> wrote in message
news:7fedfd28-c2ca-480f-b797-2121c3bf286f at (no spam) h2g2000vbd.googlegroups.com...
Quote: 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 |
|
|
| Back to top |
|
|
|
| Russell Fields... |
| Posted: Fri Oct 23, 2009 9:16 pm |
|
|
|
Guest
|
Bill,
I believe that they need the view definition right. If you are happy with
view definition being public, then:
grant view definition to public -- or whoever needs the right
RLF
"Bill" <bill.parrott at (no spam) gmail.com> wrote in message
news:7fedfd28-c2ca-480f-b797-2121c3bf286f at (no spam) h2g2000vbd.googlegroups.com...
Quote: 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 |
|
|
| Back to top |
|
|
|
| Sylvain Lafontaine... |
| Posted: Fri Oct 23, 2009 9:32 pm |
|
|
|
Guest
|
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)
"Russell Fields" <russellfields at (no spam) nomail.com> wrote in message
news:%23pFkZSAVKHA.2932 at (no spam) TK2MSFTNGP04.phx.gbl...
Quote: Bill,
I believe that they need the view definition right. If you are happy with
view definition being public, then:
grant view definition to public -- or whoever needs the right
RLF
"Bill" <bill.parrott at (no spam) gmail.com> wrote in message
news:7fedfd28-c2ca-480f-b797-2121c3bf286f at (no spam) h2g2000vbd.googlegroups.com...
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
|
|
|
| Back to top |
|
|
|
| Bill... |
| Posted: Fri Oct 23, 2009 9:40 pm |
|
|
|
Guest
|
| Thanks for the help, it solved my problem. |
|
|
| Back to top |
|
|
|
|
