SELECT Permission Denied on object 'tablename'...#229

[barbola]

I have an Access2000 front end to a SQL 2008 database (Dynamics GP) that has worked since we moved to the new server a few months ago, but has been finicky with DSN's.

Today a user is getting this error and after trying to fix it, I now get the error on my workstation.

ODBC-call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on teh object '{tablename}', databse '{dbname}', shcema 'dbo'.(#229).

I checked and dbo is the schema owner thingy.
It is a SQL user I created using SQL authentication.
Did I mention it has worked until today and nothing has changed?
I tried using SQL profile to no avail. It is greek to me and I am the only SQL Admin we have.

 

[jrbarnett]

Is dbo the table owner?

Another check - login to management studio (or from the command line using sqlcmd) using the SQL username and password that your app uses.
Run the sql select statement

SELECT * FROM {tablename}

(putting your table name in)
Does it work, does it give the same error message?
Check - are you in the right database - ie has one got a forced default database?

Can you connect using DSN less connections? it makes managing connections a lot easier.

John

 

[barbola]

dbo is the owner

It works on another workstation that has the same SQL password for the linked tables. I got this first user to work by re-selecting his local DSN instead of one on the network. But now when our receptionist tries to use it, I know she will get an error and I will have to point it to her DSN, and then the other user will get an error.

If I wanted to use DSN'less connections that will be about 2 months work for me because these are just access tables linked to SQL and I am lousy at programming.

 

[jrbarnett]

Your comment implies that you use different passwords on different workstations ("another workstation that has the same SQL password for linked tables") - what happens if you use the same username and password?

The only reasons I can think of for using different accounts are for:
* different permissions for different security roles
* auditing access to data

Also what happens if you recreate exactly the working ODBC on another computer, removing the old ones? Same name, type, driver and settings.

John

 

[barbola]

Your comment implies that you use different passwords on different workstations ("another workstation that has the same SQL password for linked tables") - what happens if you use the same username and password?"

They are using the same username and password that I created on the SQL Server in order to avoid using the 'sa' login.

Why does it work on one workstation and not the other is what I'd like to know.

I also think it has to do with the DSN but how do I fix that? Each workstation has a DSN connection to the SQL database and they use the same user and password (SQL authentication).

 

[barbola]

I also logged in to SQL Server Mgmt Studio using the same username and password that this Access db uses, and was able to run the SELECT statement with no errors.

I even created a DSN on the network and it worked for USER1 but it didn't work for USER2 until I changed his back to using the DSN on his program files ODBC folder.

After I did that, USER3 got the error, so I had to reset hers to her local ODBC folder. Now USER2 is getting the error again, and I can bet USER1 will also get errors.

It has always just worked for me so I never had to really understand how it all works, and therefore I am not able to troubleshoot this, nobody here is.

 

[jrbarnett]

Do user1, user2 and user3 have the same permissions within SQL Server? (ideally: same roles)
Is the ODBC data source configured identically - same name, same driver and same settings for different users? Are they different users on teh same pc or different pc's?

John