July 6, 2011 at 9:10 am
Hello,
A user has a role on SQL Server 2008 to SELECT from tables (no update, insert, or delete).
She would like to work with the data through an Access 2007 front end.
She opens Access, creates a blank database, clicks External Data, clicks ODBC database, creates an ODBC connection using
SQL Server Native Client 10.0. She goes through the wizard and at the end, clicks Test Connection and
gets a Success message. Her SQL Server role is assigned to her windows login. She signs on to SQL Server with
windows authentication, not a SQL Server ID. In Access, it's the same: the DSN (SQL Server Native Client 10.0)
was set up with Integrated Windows authentication.
She then tries to link to one of the SQL Server tables and gets "The
Microsoft Access database engine could not find the object <name>. Make sure the object exists
and that you spell its name and the path name correctly. (Error 3011)."
I have SELECT, UPDATE, INSERT, and DELETE access to the SQL Server table. I do the exact same thing and it works.
She tried the same thing in Access with all the SQL Server ODBC drivers: SQL Native Client, SQL Server,
and SQL Server Native Client 10.0. No luck. She is able to import perfectly from Excel using her read-only login,
so I'm thinking there's a bug in the Access Drivers. Or, there's an undocumented reason why Access 2007 won't let
me link or copy read-only tables from SQL Server 2008.
This is not specific to the user, by the way. We are all running Windows 7 Enterprise
on our laptops with Access 2007. Any of us who are given read-only permissions on the
SQL Server 2008 tables can duplicate the problem and any of us with full read/write (select,insert,update,delete)
permissions can import w/o the error message above. And as I mentioned, Excel's import works like a charm.
My problem is that I want to maintain some control over the data on the back end (SQL Server 2008 - I want
to keep the SQL Server roles if possible), but the users want to use Access (not Excel) as a front end.
Thanks,
Seth
July 6, 2011 at 9:20 am
Did you create a System DSN or a User DSN on her PC? I've seen this happen occasionally when a System DSN was used. If that is your case, delete the System DSN, and recreate it as a User DSN and I suspect it will work.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
July 6, 2011 at 9:50 am
WendellB (7/6/2011)
Did you create a System DSN or a User DSN on her PC? I've seen this happen occasionally when a System DSN was used. If that is your case, delete the System DSN, and recreate it as a User DSN and I suspect it will work.
It doesn't work with either a System or User DSN (or a machine DSN for that matter).
July 6, 2011 at 10:04 am
I use this same scenario with a number of different clients, some using Access 2003, some using 2007 and some using 2010, so I don't think it is an Access driver problem. You should be using the Native Client 10 - it's the most reliable of the SQL Server drivers when working with Access. Have you tried giving the user full permissions on just one table to see if it works?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
July 6, 2011 at 11:03 am
WendellB (7/6/2011)
I use this same scenario with a number of different clients, some using Access 2003, some using 2007 and some using 2010, so I don't think it is an Access driver problem. You should be using the Native Client 10 - it's the most reliable of the SQL Server drivers when working with Access. Have you tried giving the user full permissions on just one table to see if it works?
Yes, I am the DBA on the SQL Server side, so when I use Access, I can import or link to any of the tables. My laptop is the same image as the users' images - Windows 7 Enterprise with MS Office 2007 (Access).
July 7, 2011 at 7:31 am
I think this is the issue which led immediately to its resolution. Here is the definition of a typical table on my SQL Server box:
CREATE TABLE Request_Activity
(
XI_master_id bigint NOT NULL,
request_date datetime NULL,
last_user_to_modify last_user_to_modify_type,
last_modification_time last_modification_time_type
)
The last two columns are user-defined types. A user or role on SQL Server must have explicit permission on user-defined types in order to see them. Once I granted permission on the user-defined types, the problem disappeared. Nothing to do with DSNs or connectivity. Not an Access issue particularly, except for the generally misleading error message.
Thank you for your interest.
July 7, 2011 at 7:53 am
Glad you were able to solve it - those kind of problems can drive you up the wall! Unfortunately the Access error messages when dealing with ODBC linked tables often tend to either obscure the real problem, or are totally misleading. Actually, I think the issue is more with error handling in ODBC than with Access, but it usually gets the blame. :unsure:
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
July 8, 2011 at 5:37 pm
Does the role that she belongs to have a deny to that object or is that role a member of the db_denydatareader or something to that effect? What happens if you temporarily add yourself to "this" role?
If a user did not have a select permission then they should not be able to select the table to link.
What happens if you temporary remove her from the role and grant her select permission to the table?
What if you add a user to this role that can link to the table (remove link and then relink).
What happens if you give her a copy of an mdb that already has the table linked, can she open the table?
Verify the user account that is used to create the DSN. Is it her account?
When you test the DSN you are only testing connectivity and selecting a default database. You are not testing permissions to link to or open a table.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply