September 14, 2009 at 6:32 am
Hi
We have a database which someone is connecting too via Access 2007 (also tried on earlier versions).
When the user is a sysadmin all is fine when not all fails - they are dob on the database.
The tables are linked tables in access 2007
This only fails if the table has a primary key on it (which they all do bar one for testing)
The user is set up as the DBO in the database.
If you select the table to show data you get "ODBBC -- call failed" and nothing else - teh table is populated with #Name?.
In profiler I can see that this triggered a select statement on the table asking for the Primary Key columns only.
Nothing shows up as failing on the sql side but I do get Attention as a logged event.
This is all at the development stage and I want to sort this out before it goes any further - ie I don't want anyone getting sysadmin on the live server.
Thanks for any help you may have.
September 14, 2009 at 6:49 am
Extending the trace to all dbs I get the error message I needed:
The EXECUTE permission was denied on the object 'sp_execute', database 'mssqlsystemresource', schema 'sys'.
I'm not sure why it calls this though.
September 14, 2009 at 6:59 am
So...
I created a role in master called accessusers and gave that execute on the proc and then gave the user that role in master.
It still feels slightly dissatisfying though.
September 14, 2009 at 7:19 am
Seth Lynch (9/14/2009)
So...I created a role in master called accessusers and gave that execute on the proc and then gave the user that role in master.
It still feels slightly dissatisfying though.
I could be wrong, but I would hazard a guess that access probably does a RPC call using sp_prepare and sp_execute. and that is why it needs access to run that procedure.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 14, 2009 at 7:29 am
The puzzle for me is that if the table has no primary key defined it works with out any extra permissions. When you link the table access asks for a distinct column and is then happy to display the data straight.
It's all my fault for locking down the server in the first place:-)
September 14, 2009 at 7:31 am
Seth Lynch (9/14/2009)
It's all my fault for locking down the server in the first place:-)
Not saying a thing 😛
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply