Automatic fix for tempdb error related to 'ASPStateTempSessions'

Question

As per this how-to, I've successfully configured IIS on my XP-SP3 dev box for SQL Server 2008 Express to save ASP.NET session state information. I'm just using SQL Server because otherwise on every recompile, I was losing the session state which was obnoxious (having to re-login). But, I'm facing an annoying issue in that every time I restart SQL there's this error, and sometimes one or two other very similar friends:

The SELECT permission was denied on the object 'ASPStateTempSessions',
database 'tempdb', schema 'dbo'.

To fix the error, I just open Management Studio and edit the User Mapping for the login/dbo I'm using on the ASPState db, and re-add tempdb to that user with all but deny permissions. Apparently, once the right permissions are there, ASP.NET is able to automatically create the tables it uses. It just can't run that CreateTempTables sproc until the right security is there.

THE QUESTION... Is there a way to not have to re-do this on every restart of the SQL Server?

I don't really care right now about keeping the temp data across restarts, but I would like to not have to go through this manual step just to get my web app working on localhost, which uses session state variables throughout. I suppose one could resort to some kind of stored procedure within SQL Server to accomplish the task for this machine when the service starts, to not have to do it manually. I'd accept such an answer as a quick fix. But, I'm also assuming there's a better recommended configuration or something. Not seeing an answer to this on the how-to guide or elsewhere here on StackOverflow.

Answer 1

Both answers seem valid; but with most things Microsoft, its all in the setup...

First uninstall the ASPState database by using the command:

aspnet_regsql –ssremove –E -S .

Note:

-E is to indicate you want to use integrated security connection.

-S informs what SQL server and SQL instance to use, and the "." (dot) specifies default local instance

Then re-install using the command:

aspnet_regsql –ssadd –sstype p –E -S .

Note:

The sstype has three options, t | p | c ... the first "t", tells the installer to host all stored procedures in the ASPState database, and all data in the tempdb. The second option "p" tells the installer to persist data to the ASPState database. The last option "c" allows you to specify a different 'custom' database to persist the session state data.

If you reinstall using the "-sstype p" you then need only to supply datareader/datawriter to the ASPState database for the user that's making the connection (in most cases, the application pool's identity in IIS).

The added benefit of persisting the data is that session state is retained even after a restart of the service. The only drawback is that you need to ensure the agent cleanup job is pruning old sessions regularly (it does this by default, every minute).

Important:

If you are running a cluster, you must persist session data. You're only option is to use sstype 'p' or 'c'.

Hope this sheds light on the issue!

Answer 2

For the record, I did find a way to do this.

The issue is that the tempdb is recreated from the model db each time the service restarts. The gist of the solution is to create a stored procedure that does the job, and then make that procedure run at startup.

Source code (credit to the link above) is as follows:

use master
go

-- remove an old version
drop proc AddAppTempDBOwner
go

-- the sp
create proc AddAppTempDBOwner as
declare @sql varchar(200)
select @sql = 'use tempdb' + char(13) + 'exec sp_addrolemember ''db_owner'', ''app'''
exec (@sql)
go

-- add it to the startup
exec sp_procoption 'AddAppTempDBOwner', 'startup', 'true'
go