RE: Session state : SQLServer permissions

I understand how to create the session state databases but the documentation
doesn't say what sql permissions the account in the web site's web.config
connection string needs. It seems wrong to give it execute rights on all
stored procedures in ASPState.

So for the connection string account in the web.config...
What permissions does the account need in ASPState?
What permissions does the account need in tempdb?
Tempdb is recreated on system startup so does the ASPState_Startup procedure
need modifying to reapply the tempdb permissions?

Thanks,
Andrew




""Walter Wang [MSFT]"" wrote:

Hi Andrew,

Based on my understanding, you're using sqlserver to store ASP.NET session
state and you want to know which permissions are required to use related
session state databases at run time. Please feel free to correct me if I've
misunderstood anything.

First, we need to understand that those two tables created in tempdb or
ASPState are executed by a startup stored procedure in master. Since a
startup stored procedure is executed by the sqlserver service account when
the last database is recovered at startup
(http://msdn2.microsoft.com/en-us/library/aa258259(SQL.80).aspx), the login
account that used to connect to the sqlserver doesn't need to have this
permission.

Based on whether or not you're using tempdb or ASPState to store the
session state, your login account will need read/write permission to tempdb
or ASPState; but not both.

Please refer to following document:

#ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)
http://msdn2.microsoft.com/en-us/library/ms229862(vs.80).aspx
(search for "Session State Options")

t - Temporary. Session state data is stored in the SQL Server tempdb
database. Stored procedures for managing session state are installed in the
SQL Server ASPState database. Data is not persisted if you restart SQL.
This is the default.

p - Persisted. Both session state data and stored procedures are stored in
the SQL Server ASPState database.


If you use following commands to extract two sql scripts, you will find the
default one uses tempdb to create the tables; and the "p" option will use
ASPState to create the tables:

aspnet_regsql -ssadd -sstype t -S <server> -E -sqlexportonly tempdb.sql
aspnet_regsql -ssadd -sstype p -S <server> -E -sqlexportonly ASPState.sql


Hope this helps.


Regards,
Walter Wang (wawang@xxxxxxxxxxxxxxxxxxxx, remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.


.