ASPState Select Permission Denied

I've been scouring the 'Net and I'm surprised to not find any info on this. Apparently I've screwed up something that nobody else has managed to screw up.

Using Win03EE and SQL2005.

I created the ASPState database using "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Aspnet_regsql.exe" using "-sstype p" to put the data in ASPState instead of tempdb.

During the creation I used an admin SQL login (let's call it "MyAdmin") that has full admin permissions. (MyAdmin is a SQL login, not a domain account.) I then created an "ASPState" SQL login (not domain) and gave it full permissions within the ASPState database.

I should probably note that the Win03EE server is also a domain controller even though the SQL logins I'm using are non-domain. This is because, currently, the development workstations are not part of the server's domain. Since the entire environment is strictly for research and development, optimum security is not the first concern at this point.

When I run the ASP.NET 2.0 web application with the connection string set to use the MyAdmin account, everything works fine. I can even poke around in ASPState with SSMS and see the session entries being inserted in the tables.

When I run the application with the connection string set to use the "ASPState" login, I get "SELECT permission denied on object 'sysobjects', database 'mssqlsystemresource', schema 'sys'".

If I give ASPState sysadmin priviledges, it works. Of course, I don't want to give ASPState sysadmin priviledges.

I have seen non-ASPState issues posted on the 'Net involving permission denied and mssqlsystemresource. From those I've come to understand that this likely has to do with the changes made to SQL2005 that are probably not anticipated by the current version of aspnet_reqsql.

So... can anyone help me save what's left of my hair from being pulled out any further? I've been clicking various permission settings on and off all day long with no results. The lack of correlation between mssqlsystemresource and what permission setting is needed where is pretty frustrating.

In addition to the solution to this problem, I'd really appreciate any info anyone has on a great place to go read about security setting adjustment considerations in light of the changes made in SQL2005. In particular, what changes from SQL2000 to SQL2005 impact the operation of existing SQL2000 code and what an admin should know about adjusting security settings.

Thanks!

I was about to explain the reason with a resolution by referring a KBA, but found this interesting page http://idunno.org/dotNet/sessionState.aspx on web in this regard.

ASPState DB Permissions

I need to find out what the 'Required' permissions on on the ASPState database? We have been granting dbo to the login. Thanks.

I finaly figured out how to do this, I created a database role. and assigned implicit permissions to the stored procedures for the ASPState database. The assigned the owner to be dbo. Then I assigned the custom role to the login that needed access. This way I avoided applying db_owner permissions to the ASPState database for my login.

ASPState database transaction log out control

I am developing an Asp.Net web site which is using SQL Server session state
being held in the ASPState database. I noticed recently that the ASPState
database transaction log had grown to 7.5GB despite the face that only a
small number of users were using the web site which is still under
development. Any reason for this runaway growth? Build as below
..Net Framework 1.1
Windows Server 2003 Standard Edition SP1
Sql Server 2000 Standard Edition SP4
Scott
Hi
Your are in Full recovery mode and are not backup up your log.
http://msdn.microsoft.com/library/de...kprst_565v.asp
http://www.dbazine.com/sql/sql-artic...lins-sqlserver
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"scottrm" <scottrm@.newsgroup.nospam> wrote in message
news:36A7060E-C318-46AC-B128-DB491B883A0B@.microsoft.com...
>I am developing an Asp.Net web site which is using SQL Server session state
> being held in the ASPState database. I noticed recently that the ASPState
> database transaction log had grown to 7.5GB despite the face that only a
> small number of users were using the web site which is still under
> development. Any reason for this runaway growth? Build as below
> .Net Framework 1.1
> Windows Server 2003 Standard Edition SP1
> Sql Server 2000 Standard Edition SP4
> --
> Scott
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVSUQoL$FHA.3096@.tk2msftngp13.phx.gbl...
> Hi
> Your are in Full recovery mode and are not backup up your log.
>
You should run the ASP.NET state database in simple recovery mode unless you
are trying to do log shipping or something with it. Better yet, use the
script to create all its objects in TempDB intead of their own database.
David
|||Hi Scott,
You may also refer the articles below for more information
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/kb/317375/en-us
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/kb/873235/en-us
Topic: DBCC SHRINKDATABASE / DBCC SHRINKFILE in BOL
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner 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.

ASPState database transaction log out control

I am developing an Asp.Net web site which is using SQL Server session state
being held in the ASPState database. I noticed recently that the ASPState
database transaction log had grown to 7.5GB despite the face that only a
small number of users were using the web site which is still under
development. Any reason for this runaway growth' Build as below
.Net Framework 1.1
Windows Server 2003 Standard Edition SP1
Sql Server 2000 Standard Edition SP4
--
ScottHi
Your are in Full recovery mode and are not backup up your log.
http://msdn.microsoft.com/library/d... />
t_565v.asp
http://www.dbazine.com/sql/sql-arti...llins-sqlserver
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"scottrm" <scottrm@.newsgroup.nospam> wrote in message
news:36A7060E-C318-46AC-B128-DB491B883A0B@.microsoft.com...
>I am developing an Asp.Net web site which is using SQL Server session state
> being held in the ASPState database. I noticed recently that the ASPState
> database transaction log had grown to 7.5GB despite the face that only a
> small number of users were using the web site which is still under
> development. Any reason for this runaway growth' Build as below
> .Net Framework 1.1
> Windows Server 2003 Standard Edition SP1
> Sql Server 2000 Standard Edition SP4
> --
> Scott|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVSUQoL$FHA.3096@.tk2msftngp13.phx.gbl...
> Hi
> Your are in Full recovery mode and are not backup up your log.
>
You should run the ASP.NET state database in simple recovery mode unless you
are trying to do log shipping or something with it. Better yet, use the
script to create all its objects in TempDB intead of their own database.
David|||Hi Scott,
You may also refer the articles below for more information
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/kb/317375/en-us
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/kb/873235/en-us
Topic: DBCC SHRINKDATABASE / DBCC SHRINKFILE in BOL
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner 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.

ASPState database transaction log out control

I am developing an Asp.Net web site which is using SQL Server session state
being held in the ASPState database. I noticed recently that the ASPState
database transaction log had grown to 7.5GB despite the face that only a
small number of users were using the web site which is still under
development. Any reason for this runaway growth' Build as below
.Net Framework 1.1
Windows Server 2003 Standard Edition SP1
Sql Server 2000 Standard Edition SP4
--
ScottHi
Your are in Full recovery mode and are not backup up your log.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp
http://www.dbazine.com/sql/sql-articles/mullins-sqlserver
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"scottrm" <scottrm@.newsgroup.nospam> wrote in message
news:36A7060E-C318-46AC-B128-DB491B883A0B@.microsoft.com...
>I am developing an Asp.Net web site which is using SQL Server session state
> being held in the ASPState database. I noticed recently that the ASPState
> database transaction log had grown to 7.5GB despite the face that only a
> small number of users were using the web site which is still under
> development. Any reason for this runaway growth' Build as below
> .Net Framework 1.1
> Windows Server 2003 Standard Edition SP1
> Sql Server 2000 Standard Edition SP4
> --
> Scott|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVSUQoL$FHA.3096@.tk2msftngp13.phx.gbl...
> Hi
> Your are in Full recovery mode and are not backup up your log.
>
You should run the ASP.NET state database in simple recovery mode unless you
are trying to do log shipping or something with it. Better yet, use the
script to create all its objects in TempDB intead of their own database.
David|||Hi Scott,
You may also refer the articles below for more information
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/kb/317375/en-us
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/kb/873235/en-us
Topic: DBCC SHRINKDATABASE / DBCC SHRINKFILE in BOL
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner 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.

ASPState database

Hi,
We experienced poor performance when we actually started storing the .NET
VIEWSTATE information inside the ASPState database. We were experiencing
high volume of locks, avg. disk queue lenghts of over 40 and poor page life
expectancy (around 100). It looked like our 4 processor DELL PowerEdge 6850
was memory bound. The data file grew big to 8.8 GB in less than 2 hours. We
don't know what is really going on here. The schema for ASPState database
just involves 3 tables really and a SQL job that runs every minute to delete
records that have an expired session.
Here are the tables involved in this database. Could you help me find if
there is anything fundamentally wrong? We are going by the book on what
microsoft has recommended in terms of the database and implementing the
Session state for .NET on the server side.
CREATE TABLE [ASPStateTempSessions](
[SessionId] [char](32) NOT NULL,
[Created] [datetime] NOT NULL,
[Expires] [datetime] NOT NULL,
[LockDate] [datetime] NOT NULL,
[LockDateLocal] [datetime] NOT NULL,
[LockCookie] [int] NOT NULL,
[Timeout] [int] NOT NULL,
[Locked] [bit] NOT NULL,
[SessionItemShort] [varbinary](7000) NULL,
[SessionItemLong] [image] NULL,
PRIMARY KEY CLUSTERED
(
[SessionId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Index_Expires] ON [ASPStateTempSessions]
(
[Expires] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ASPStateTempApplications](
[AppId] [int] NOT NULL,
[AppName] [char](280) NOT NULL,
PRIMARY KEY CLUSTERED
(
[AppId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Index_AppName] ON [ASPStateTempApplications]
(
[AppName] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ASPStateDeferredData](
[SessionId] [char](32) NOT NULL,
[KeyID] [char](48) NOT NULL,
[SessionItemLong] [image] NULL,
CONSTRAINT [PK__ASPStateDeferredData] PRIMARY KEY CLUSTERED
(
[SessionId] ASC,
[KeyID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOHi
You don't say what volume of messages were being written?
You can run SQL Profiler to see the activity on the machine and to report
blocking, lock escallation etc. Check any queries especially how the deletion
process to see if you using the indexes, or if changing the index to be
clustered/non-clustered or if you require different indexes.
Also read http://support.microsoft.com/kb/271509/EN-US/ on how to monitor
blocking.
These changes should be done in a test environment rather than on the live
system.
John
"Shiva" wrote:
> Hi,
> We experienced poor performance when we actually started storing the .NET
> VIEWSTATE information inside the ASPState database. We were experiencing
> high volume of locks, avg. disk queue lenghts of over 40 and poor page life
> expectancy (around 100). It looked like our 4 processor DELL PowerEdge 6850
> was memory bound. The data file grew big to 8.8 GB in less than 2 hours. We
> don't know what is really going on here. The schema for ASPState database
> just involves 3 tables really and a SQL job that runs every minute to delete
> records that have an expired session.
> Here are the tables involved in this database. Could you help me find if
> there is anything fundamentally wrong? We are going by the book on what
> microsoft has recommended in terms of the database and implementing the
> Session state for .NET on the server side.
> CREATE TABLE [ASPStateTempSessions](
> [SessionId] [char](32) NOT NULL,
> [Created] [datetime] NOT NULL,
> [Expires] [datetime] NOT NULL,
> [LockDate] [datetime] NOT NULL,
> [LockDateLocal] [datetime] NOT NULL,
> [LockCookie] [int] NOT NULL,
> [Timeout] [int] NOT NULL,
> [Locked] [bit] NOT NULL,
> [SessionItemShort] [varbinary](7000) NULL,
> [SessionItemLong] [image] NULL,
> PRIMARY KEY CLUSTERED
> (
> [SessionId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE NONCLUSTERED INDEX [Index_Expires] ON [ASPStateTempSessions]
> (
> [Expires] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [ASPStateTempApplications](
> [AppId] [int] NOT NULL,
> [AppName] [char](280) NOT NULL,
> PRIMARY KEY CLUSTERED
> (
> [AppId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE NONCLUSTERED INDEX [Index_AppName] ON [ASPStateTempApplications]
> (
> [AppName] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [ASPStateDeferredData](
> [SessionId] [char](32) NOT NULL,
> [KeyID] [char](48) NOT NULL,
> [SessionItemLong] [image] NULL,
> CONSTRAINT [PK__ASPStateDeferredData] PRIMARY KEY CLUSTERED
> (
> [SessionId] ASC,
> [KeyID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
>

ASPState database

Hi,
We experienced poor performance when we actually started storing the .NET
VIEWSTATE information inside the ASPState database. We were experiencing
high volume of locks, avg. disk queue lenghts of over 40 and poor page life
expectancy (around 100). It looked like our 4 processor DELL PowerEdge 6850
was memory bound. The data file grew big to 8.8 GB in less than 2 hours. We
don't know what is really going on here. The schema for ASPState database
just involves 3 tables really and a SQL job that runs every minute to delete
records that have an expired session.
Here are the tables involved in this database. Could you help me find if
there is anything fundamentally wrong? We are going by the book on what
microsoft has recommended in terms of the database and implementing the
Session state for .NET on the server side.
CREATE TABLE [ASPStateTempSessions](
[SessionId] [char](32) NOT NULL,
[Created] [datetime] NOT NULL,
[Expires] [datetime] NOT NULL,
[LockDate] [datetime] NOT NULL,
[LockDateLocal] [datetime] NOT NULL,
[LockCookie] [int] NOT NULL,
[Timeout] [int] NOT NULL,
[Locked] [bit] NOT NULL,
[SessionItemShort] [varbinary](7000) NULL,
[SessionItemLong] [image] NULL,
PRIMARY KEY CLUSTERED
(
[SessionId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Index_Expires] ON [ASPStateTempSessions]
(
[Expires] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ASPStateTempApplications](
[AppId] [int] NOT NULL,
[AppName] [char](280) NOT NULL,
PRIMARY KEY CLUSTERED
(
[AppId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Index_AppName] ON [ASPStateTempApplicatio
ns]
(
[AppName] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ASPStateDeferredData](
[SessionId] [char](32) NOT NULL,
[KeyID] [char](48) NOT NULL,
[SessionItemLong] [image] NULL,
CONSTRAINT [PK__ASPStateDeferredData] PRIMARY KEY CLUSTERED
(
[SessionId] ASC,
[KeyID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOHi
You don't say what volume of messages were being written?
You can run SQL Profiler to see the activity on the machine and to report
blocking, lock escallation etc. Check any queries especially how the deletio
n
process to see if you using the indexes, or if changing the index to be
clustered/non-clustered or if you require different indexes.
Also read http://support.microsoft.com/kb/271509/EN-US/ on how to monitor
blocking.
These changes should be done in a test environment rather than on the live
system.
John
"Shiva" wrote:

> Hi,
> We experienced poor performance when we actually started storing the .NET
> VIEWSTATE information inside the ASPState database. We were experiencing
> high volume of locks, avg. disk queue lenghts of over 40 and poor page lif
e
> expectancy (around 100). It looked like our 4 processor DELL PowerEdge 685
0
> was memory bound. The data file grew big to 8.8 GB in less than 2 hours. W
e
> don't know what is really going on here. The schema for ASPState database
> just involves 3 tables really and a SQL job that runs every minute to dele
te
> records that have an expired session.
> Here are the tables involved in this database. Could you help me find if
> there is anything fundamentally wrong? We are going by the book on what
> microsoft has recommended in terms of the database and implementing the
> Session state for .NET on the server side.
> CREATE TABLE [ASPStateTempSessions](
> [SessionId] [char](32) NOT NULL,
> [Created] [datetime] NOT NULL,
> [Expires] [datetime] NOT NULL,
> [LockDate] [datetime] NOT NULL,
> [LockDateLocal] [datetime] NOT NULL,
> [LockCookie] [int] NOT NULL,
> [Timeout] [int] NOT NULL,
> [Locked] [bit] NOT NULL,
> [SessionItemShort] [varbinary](7000) NULL,
> [SessionItemLong] [image] NULL,
> PRIMARY KEY CLUSTERED
> (
> [SessionId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE NONCLUSTERED INDEX [Index_Expires] ON [ASPStateTempSessions
]
> (
> [Expires] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [ASPStateTempApplications](
> [AppId] [int] NOT NULL,
> [AppName] [char](280) NOT NULL,
> PRIMARY KEY CLUSTERED
> (
> [AppId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE NONCLUSTERED INDEX [Index_AppName] ON [ASPStateTempApplicat
ions]
> (
> [AppName] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [ASPStateDeferredData](
> [SessionId] [char](32) NOT NULL,
> [KeyID] [char](48) NOT NULL,
> [SessionItemLong] [image] NULL,
> CONSTRAINT [PK__ASPStateDeferredData] PRIMARY KEY CLUSTERED
> (
> [SessionId] ASC,
> [KeyID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
>

ASPState and Windows vista

There is no ASPNET account on Windows Vista. Wich account do I need to use on ASPState database ?

Thanks

Simply use / or create another account for accessing the databases or use the network service account:

http://msdn2.microsoft.com/en-us/library/ms998320.aspx

Jens K. Suessmeyer

http://www.sqlserver2005.de