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
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.
The best 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.