If the SQL server session state is used (which is by default in the EP.NET installer) an SQL server user ("EPUser" in this example) should have the 'db_owner' permission for the following databases:
- an EP.NET database
- "ASPState"
- "tempdb"
Other session state storage method - InProc - requires the 'db_owner' permission to be set only for the EP.NET database.
Note that a user permissions for the "tempdb" database are lost when SQL server service is restarted because the "tempdb" database is recreated every time when SQL server is started.
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 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 after 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.NET DB is located, "yoursqluser" - a name of SQL server administrator, usually 'sa', "yourpassword" - a password for this user.
NOTE.
If you run the installer in any mode including the Upgrade one you must use SQL server administrator (usually 'sa') user on the database installation screen of the installer.
Article ID: 65, Created On: 8/8/2008, Modified: 11/9/2011