Archive for the ‘Linked Tables’ Category

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.