Linked Tables from access failing if they have a primary key.

Posted: September 26, 2009 in Linked Tables, MS Access, SQL 2005

We had someone trying to connect to a table in a database using access and linked tables. They kept getting ODBC – call failed when they tried to access the data. If a table didn’t have a primary key then it worked!
I ran a trace and saw it was calling a select statement against the Primary Key in that table. There were no errors but an attention warning appeared. I then spread the trace to all databases and go this error:
The EXECUTE permission was denied on the object ‘sp_execute’, database ‘mssqlsystemresource’, schema ‘sys’.
I had locked down this server so now had to open it up a little.
I created a role in Master called AccessUsers. I granted the role Execute on ‘sp_execute’.
Now they can connect using access.
I’ll show the lock-down scripts in another entry.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s