Automatically fix tempdb error related to "ASPStateTempSessions"

Question

According to how-to , I successfully configured IIS on my XP-SP3 Dev field for SQL Server 2008 Express to save ASP.NET session state information, I just use SQL Server, because otherwise I would lose every recompilation Session state that was unpleasant (need to re-enter the system). But I ran into the annoying problem that every time I restart SQL there is this error, and sometimes with one or two other very similar friends:

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

In order to fix the error, I just open Management Studio and edit the user mapping for login / DBO I use ASPState on db and re-add tempdb for this user with all but deny permissions. Apparently, after the right permissions are, ASP.NET can automatically create the tables that it uses. It simply cannot run this CreateTempTables sproc until the correct protection appears.

QUESTION ... Is there a way not to repeat this every time I restart SQL Server?

I really don't care right now about saving temporary restart data, but I would like to not go through this manual step just to get my web application running on a local host that uses the session state variable in everything. I believe that you can use some kind of stored procedure in SQL Server to complete the task for this computer when the service starts, so as not to do it manually. I would accept such an answer as a quick fix. But I also assume that there is a recommended configuration there or something else. Seeing no answer to this in the usage guide or elsewhere here on StackOverflow.

Answer 1

Both answers seem valid; but with most of Microsoft's stuff, it's all in the setup ...

First delete the ASPState database using the command:

aspnet_regsql -ssremove -E -S.

Note:

-E means you want to use an integrated security connection.

-S tells which SQL server and SQL instance to use, and "." (dot) indicates the default local instance

Then reinstall using the command:

aspnet_regsql -ssadd -sstype p -E -S.

Note:

There are three options in sstype: t | p | c ... the first "t" tells the installer about the location of all stored procedures in the ASPState database and all data in tempdb. The second option, “p,” tells the installer to save the data in the ASPState database. The last option “c” allows you to specify another “user” database to save session state data.

If you reinstall using "-sstype p", you only need to provide the datareader / datawriter to the ASPState database for the user who makes the connection (in most cases, the application pool identifier in IIS).

An additional benefit of saving data is that the session state is maintained even after the service is restarted. The only drawback is that you need to ensure that the agent cleanup routine regularly trims old sessions (this is done by default, every minute).

Important:

If you are using a cluster, you must save the session data. You can use only sstype 'p' or 'c'.

Hope this sheds light on the problem!

Answer 2

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

The problem is that tempdb is recreated from the db model every time the service restarts. The essence of the solution is to create a stored procedure that executes the task, and then runs this procedure at startup.

The source code (credit from 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 

Answer 3

Well done in order to find the strangest way to do this.

The correct answer is:

 use master go EXEC sp_configure 'Cross DB Ownership Chaining', '1' go RECONFIGURE go EXEC sp_dboption 'ASPState', 'db chaining', 'true' go