I created a user EPUser on my SQL server for EP. Which permissions should this user have on SQL server for EP to work?

Installation & Upgrade
For EP to work properly for the case when a custom SQL server user is used the following permissions should be setup.

If the SQL server session state is used (which is by default in the EP installer) an SQL server user ("EPUser" in this example) should have the 'db_owner' permission for the following databases:
  • an EP database
  • "ASPState"
  • "tempdb"

Other session state storage methods - "In Process' or 'State Server" - require the 'db_owner' permission to be set only for the EP database.

Note that the user permissions for the "tempdb" database are lost when SQL server service is restarted because the "tempdb" database is recreated every time in such cases.

You can run the following query on your SQL server. It will add the EPUser (this is the sample name) SQL server user to the "tempdb" database with the "db_owner" role and this will be added to the startup stored procedure for the SQL server.

 

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE WITH OVERRIDE
GO
CREATE PROC spAdduser
AS
exec tempdb..sp_adduser 'EPuser', 'EPuser', 'db_owner'
GO
-- set it to run at sql server start-up
exec sp_procoption N'spAdduser', 'startup', 'on'



NOTE.
The better way to handle it is to configure ASP.NET for Persistent SQL Server Session State Management. In this case required tables are created in "ASPState" database and the session data is retained when the SQL Server service is restarted.

The result will be achieved by running the following command:

 
C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe -S yoursqlserver -U yoursqluser -P yourpassword -ssadd -sstype p

Where "yoursqlserver" - a name of your SQL server where EP DB is located, "yoursqluser" - a name of SQL server administrator, usually 'sa', "yourpassword" - a password for this user.