Description

After applying the performance optimization script from the "Optimize SQL Server performance" section of this article, users might experience issues with session database read/write operations after restarting the SQL server. SQL users configured to have access to the tempDB created by the script might no longer have such access, and Sitecore can throw session-related exceptions. This behavior is related to the fact that tempDB is recreated along with its access rights each time the SQL server is restarted.

Solution

1. Open the Sessions db performance boost.sql script and add the query marked as bold, in the following way:
   

   CREATE PROCEDURE [dbo].[Sitecore_InitializeSessionState] AS
   BEGIN
   EXECUTE [Sitecore.Sessions].[dbo].[CreateTables];
   EXEC('USE tempDb; CREATE USER [YOUR_USER] FOR LOGIN [YOUR_USER] WITH DEFAULT_SCHEMA=[dbo]; ALTER ROLE [db_datareader] ADD MEMBER [YOUR_USER]; ALTER ROLE [db_datawriter] ADD MEMBER [YOUR_USER]')
   END;
   GO

2. To execute the script, use the performance enhancement instructions from the Optimize SQL Server performance section of Walkthrough: Configuring a shared session state database using SQL.

Special Thanks

We would like to thank Rob Ahnemann from the Rock, Paper, Sitecore blog and his reader Shaun for the provided solution to this problem. The original article with this approach can be found at the following link: Sitecore Shared Session: Moving from the tempDB.