Handling issues related to ASP.net session database during migration…

You may have experienced below errors from applications after you migrated all databases of one server to another one which is hosting databases used by one or many .net applications:

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'.

These errors actually has nothing to do with SQL Server permissions. These error crates because you migrated session databases of .net (ASPState or ASPState20). These session databases are instance specific and should not be migrated with other user databases.

So to resolve this issue, advise your developers to create a session database on the new instance from scratch. As session database hold only session related data, there is no risk associated to data loss if you create a new session database in new instance.

To create new session database, refer to http://msdn.microsoft.com/en-us/library/ms178586.aspx