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

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.