SELECT permission denied trying to import data into Excel

Question

We recently migrated to a newer version of a program and migrated its database from SQL Server 2000 to SQL Server 2008 R2. We had some Excel files which ran a few custom queries to pull data. I changed the values in the connection string to reflect our 2008 R2 setup:

DRIVER={SQL Server};SERVER=SQL01;UID=PSI;Trusted_Connection=Yes;DATABASE=PSI_DB_A;

Trying to do a refresh in Excel gives the error:

The SELECT permission was denied on the object 'ARTRNTRX', database 'PSI_DB_A', schema 'dbo'.

I researched this error on this site and numerous places on the web before posting; trying some of the things suggested.

The only difference is that with SQL Server 2000, the user "sa" had access to everything and every database. We wanted to avoid that in 2008 R2. So I created the PSI user and wanted to give it limited access. We just perform SELECT queries in these few Excel sheets anyway.

Using the SSMS, under Security > Logins > PSI > Properties > User Mapping > I gave the "PSI" user the "db_datareader" role for PSI_DB_A. Then under the Databases > PSI_DB_A database > Properties > Permissions > I select PSI user and granted them the "Connect" and "Select" permissions in the list.

But I'm still getting the SELECT permission error when I try to refresh the data in Excel. I'm at a loss here.

EDIT:

After a bit more digging, I noticed when in Excel I could connect to the SQL Server using From Other Sources > From SQL Server > Enter server name > Select Database PSI_DB_A from the list and it shows me views and tables. But only certain tables are showing, not all of them.

Going back into SSMS, I expanded the table list under the database, right-click a table (on one of the tables which was showing in Excel) > Properties > Permissions. Under users/roles the user "public" is shown which is given SELECT, etc. permissions. But on 90% of the other tables, "public" is not listed.

Why does "public" need to be added at the table permission level, when I defined a login user who should have complete read authorization on all tables in the database?

Answer 1

After reviewing your error. I tried to setup in my TEST environment and I have received an error.

If you setup connection from Excel and after selecting specific tables to test go to connection properties.

You will be able yo see connection string. Try to verify it with your query and see if it is changing any parameters in connection string from your query.

Try it and let us know any progress in it.

Answer 2

It may be due to the fact that you specified Trusted_Connection=Yes in your connection string. This will have the connection be established using Windows authentication using the domain account you're currently logged in as at the client, effectively ignoring the UID parameter. Try removing that parameter and rerunning. You may need to provide the password in the connection string, but since that's not secure, you could also keep the Trusted Connection parameter, put your users' Windows logins in a domain group and grant the domain group SELECT privs to the appropriate objects.