The SELECT permission was denied on the object '<Table_Name>', database '<Database_Name>', schema 'dbo'. - ASP.NET/SQL Server

Question

I have a database "Sample". And there is a user in Active Directory (AD) called "CORPHQ\King". I have followed these steps:

1) Go to SQL Server Management Studio (2005) and "Security -> Logins".
2) Right Click -> New Login
3) Login Name : CORPHQ\King
4) Selected "Windows Authentication"
5) Default database - "Sample".
6) Under "User Mapping" section, Selected the "Sample" database, User as "CORPHQ\King" and selected "dbo" as default schema.
7) Selected "db_owner" as "Database role membership for - Sample".

Now in the WEB.CONFIG of the ASP.NET application, i do have:

<connectionStrings>
    <add name="DB" connectionString="Data Source=128.127.126.25; database=Sample; user=corphq\King; password=XXXXXXXXXXX; Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
</connectionStrings>

Now when i ran the ASP.NET application, i am getting the error mentioned:

Error:

The SELECT permission was denied on the object 'tbl_Student', database 'Sample', schema 'dbo'

I think so user "CORPHQ\King" has the "db_owner" access to "Sample" database. Am i correct ? Where am i missing ?

Thanks

Answer 1

Since you're specifying Integrated Security in your connection string, you shouldn't be specifying a username and password when using Windows Authentication.

Instead, you will be authenticating as whatever domain account ASP.NET is running under, which is probably the network service account.

Your application is likely connecting as a totally different user than CORPHQ\King, and this other login doesn't have the permissions for this operation.

For more details, see How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0

Answer 2

You may have given it access to connect to the database, but there should be further steps to GRANT the user specific permissions such as SELECT, INSERT, UPDATE and DELETE on tables in that database.

Answer 3

Solution is for this problem is that open the dbo schema Go to property and click on View database permission link. Give the database select ,delete edit permission to DOMAIN\MACHINENAME$. user.