The SELECT permission was denied on the object 'aaa', database 'bbb', schema 'dbo'

Question

I have a .net core application which hosts on Window Server 2012 IIS in production. One of screen is to display the result from a select statement.

But I got the error

`The SELECT permission was denied on the object 'aaa', database 'bbb', schema 'dbo'.

The thing is that I can run the select query from Microsoft SQL Server Management Studio successfully.

The connection string in my code is

"Server=MyServer, Database=MyDatabase;Integrated Security=True"

I don't have an user account on the connection string, so please don't advise me to right click security\Users node on the database to grant the user read and write permission etc.

I think is it some set up in Windows Server?

Update: In our test QA site, we have another Windows Server 2012. The same code, however the test web site just runs fine. So I think that there is a difference but I couldn't figure it out. The application folder security setting are same.

Whatever account IIS is running under, it does not have access to table you are trying to SELECT from. — Larnu
The error is pretty clear. I'd suspect the application pool account wasn't added to the database at all, or if it was, it only has the public role which doesn't allow it to read anything — Panagiotis Kanavos
@PanagiotisKanavos, what is the application pool account? Identity? I was told that I can't grant the select to public role. — Hello

Joel Coehoorn Joel Coehoorn · Accepted Answer · 2020-07-28T15:37:19

When testing on your local machine, the app runs in the context of your user account. This is the account used for connecting to the database with Integrated Security.

When deployed in IIS, the app runs in the context of a web site or virtual directory that maps to an IIS Application Pool. You can see these Application Pools in IIS Manager. The Application Pool runs in the context of a specific user account, and this is the account used for Integrated Security:

You need to find this account and give it access to your database. Certain common account types, such as Network Service and ApplicationPoolIdentity, will not work well with Integrated Security out of the box. You may need to take extra steps, or even create or request a special service account in Active Directory you can use instead.

The SELECT permission was denied on the object 'aaa', database 'bbb', schema 'dbo'

1 Answers