Recently one of my clients deployed an ASP.NET web application to their public web farm.  Since this application made use of session state, they elected to store the session data in SQL Server.  The application ran fine for a couple months but then began exhibiting some problems.  Specifically, the following errors occurred:

SELECT permission denied on object 'ASPStateTempApplications', database 'tempdb', owner 'dbo'.
INSERT permission denied on object 'ASPStateTempApplications', database 'tempdb', owner 'dbo'.
SELECT permission denied on object 'ASPStateTempApplications', database 'tempdb', owner 'dbo'.

and

System.Data.SqlClient.SqlException: A severe error occurred on the current command.  The results, if any, should be discarded.
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.SessionState.SqlStateClientManager.SetAsyncWorker(String id, SessionStateItem item, Byte[] buf, Int32 length, Boolean inStorage)

Unfortunately, a search of the Microsoft KnowledgeBase didn't turn up any results.  Time for some old-fashioned sleuthing!

I found an article by Barry Dorrans that pointed to the problem being the "Cross-Database Ownership Chaining" feature and its change in behavior starting with SP3 for SQL Server 2000.

In addition to the information found there, I also discovered (thanks to Carl Prothman) that the database owner of the ASPState database still needed to be set to "sa" even if trusted connections were not being used.

To roll the whole thing up, I created the following script to set the necessary permissions for the database and to set the options referenced above.  You'll need to change all references to "aspnet_user" to the appropriate SQL Server login on your server.

USE master
go
EXEC sp_dboption 'ASPState', 'db chaining', 'true'
go

USE ASPState
GO
sp_changedbowner 'sa','true'
GO
sp_grantdbaccess 'aspnet_user', 'aspnet_user'
GO
GRANT EXEC on TempGetAppID to aspnet_user
GRANT EXEC on TempGetStateItem to aspnet_user
GRANT EXEC on TempGetStateItem2 to aspnet_user
GRANT EXEC on TempGetStateItemExclusive to aspnet_user
GRANT EXEC on TempGetStateItemExclusive2 to aspnet_user
GRANT EXEC on TempInsertStateItemLong to aspnet_user
GRANT EXEC on TempInsertStateItemShort to aspnet_user
GRANT EXEC on TempReleaseStateItemExclusive to aspnet_user
GRANT EXEC on TempRemoveStateItem to aspnet_user
GRANT EXEC on TempResetTimeout to aspnet_user
GRANT EXEC on TempUpdateStateItemLong to aspnet_user
GRANT EXEC on TempUpdateStateItemLongNullShort to aspnet_user
GRANT EXEC on TempUpdateStateItemShort to aspnet_user
GRANT EXEC on TempUpdateStateItemShortNullLong to aspnet_user
GO

Hopefully Microsoft will address this issue with a KnowledgeBase article soon!