The SELECT permission was denied on the object 'tblName', database 'dbName', schema 'dbo'

Hi,

I'm in need of a few more pointers when it comes to SQL Server 2005 Security setup for accessing a database through IIS web services. There's loads of great advise in these forums which I've followed so pls forgive me if I've missed a post that finally resolves this. I know I'm so close but it feels so very far...

Anyhow, here's the set up:

- Server 2003 / SQL Server 2005 / .NET 2.0 / Visual Studio 2005 / Client XP PC

The story so far:

- Built an ASPX website in VS2005 on a client PC that connects to a SQL2005 db. No probs during development.

- Copied website to WS2005 & configured IIS accordingly. No probs browsing non-db webpages.

The problem:

As soon as I browse to a page that extracts data from the db I get the following error message..

[SqlException (0x80131904): The SELECT permission was denied on the object 'tbl_location', database 'SmartDMA', schema 'dbo'.]

...plus a whole load of code that I've seen plastered over lots of forums.

My configurations:

- The website is in the default SQL Application Pool that has the identity set to 'Predifed: Network Service' (as I'd previously has a similar error; SQLException (blah blah): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection)

- IIS Website ASP.NET is v2.0.50727

- Website Authentication Methods / Enable Anonymous Access (ticked) Username: IUSR_SERVERNAME Password: *********. Intergration Windows Authentication (ticked).

- SQL Server Mngt, Secuirty / Logins / NT AUTHORITY\NETWORK SERVICE lists the db in question with dbo as the Default Schema & public ticket as the db role membership.

- connectionString="Data Source=SERVERNAME;Initial Catalog=DBNAME;Integrated Security=true" providerName="System.Data.SqlClient"/>

What I've tried already based on forums advise:

- Changing Server Properties to SQL Server & Windows Authentication mode.

- Rather than using Integrated Security I've tried the SQL 'sa' account, but not 100% is I did that right (still got the same error anyway).

The rest of the advise I've read appears to confirm the rest of my settings, but I'm still getting the error.

Can anyone please shed any light on what I'm missing here. Feel free to ask questions on any configuration settings I've missed that may help.

Many thanks in advance..

I would recommend using SQL Server profiler to find out what is the execution context being used at the moment the permission check fails. It may be possible that the connection is being established under a different context than the one you expected.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Thanks a lot Raul.

Your advise pointed me in the right direction. I'd previously never set the User Mapping controls in SQL Server Management Studio to allow the NT AUTHORITY\NETWORK SERVICE account datareader & writer access to the db.

It now works like a charm!

Cheers!