4

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 to your question for public role can be answered here..stackoverflow.com/questions/410396/… – Im88 Dec 11 '14 at 17:17
  • That's very helpful. I can understand why public may only be allowed as a user/role on a small handful of the tables in the database. I don't want to add the "public" user/role on the tables I need to query from in Excel. But that's the only differentiating factor I've found as to why some tables are able to be accessed and most are not. – jrob007 Dec 11 '14 at 17:52
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.

enter image description here

Try it and let us know any progress in it.

  • If you mean Import/Export Wizard in the SSMS, then no that's not an option. That's the reason we set this up in Excel for some of our end users who don't have/need direct access to the SQL server. Just a few custom queries from tables in the database.I should mention this is Excel 2010. I'm not aware of an Import/Export Wizard to map fields in Excel? It's just a simple workbook connection with a query. – jrob007 Dec 11 '14 at 16:48
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.