Wednesday, October 01, 2008 Session Error remedy

Has your SQL server restarted lately; well if you're application is reporting errors like these it might need some reconfigurations:
SELECT permission was denied on the object 'ASPStateTempSessions', database 'tempdb', schema 'dbo'.
INSERT permission was denied on the object 'ASPStateTempSessions', database 'tempdb', schema 'dbo'.
UPDATE permission was denied on the object 'ASPStateTempSessions', database 'tempdb', schema 'dbo'.

The cause of this is that your application uses SQLServer session storage. This is generally a good design pattern; the SQLServer session storage allows you to have multiple webserver and thereby scale your infrastructure. SQLSession is one of three possible session storage method and is defined in the web.config with a directive like:
<sessionstate mode="SQLServer" timeout="1440" sqlconnectionstring="Data;User ID=WebSession;Password=password" cookieless="false"></sessionstate>

However it relies upon a temporary database in SQL server. Each time the server is rebooted, the entire database is recreated along with the access permissions. Your database server is doing its rightful job of blocking non-administrative accounts from doing things without authorization.

Instant Fix. Grant permissions to you WebSession database user to tempdb. Simply make it a db_owner. This is okay, however, you'll need to repeat the step each time the server is restarted.
USE [tempdb]
EXEC sp_addrolemember 'db_owner', 'WebSession'

Temporary Relief.
Grant sysadmin privledges to the WebSession account:
EXEC master..sp_addsrvrolemember @loginame = 'WebSession', @rolename = 'sysadmin'

However granting such copious rights to this account is dangerous. Should you web application be compromised, hacked or be exposed, you could be caught with your shorts down.

Best Solution
Instead of relying on tempdb, the session data can be stored in permanent tables. Install the SQL script:

Alternatively, from the command prompt, run:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -ssadd -E -sstype p

Share Links