Healthcheck needed on a script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Healthcheck needed on a script

Author

Topic

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-12-20 : 07:26:45

Hi, can anyone tell me how the errors can be fixed on this script??

Errors:

The CREATE DATABASE process is allocating 0.75 MB on disk 'aspstate'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'aspstate_log'.
Server: Msg 15175, Level 16, State 1, Procedure sp_droplogin, Line 93
Login 'usraspstate' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login.

Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
User 'usraspstate' does not exist in the current database.
Granted database access to 'usraspstate'.
'usraspstate' added to role 'db_datareader'.
'usraspstate' added to role 'db_datawriter'.
'usraspstate' added to role 'db_owner'.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.
Server: Msg 2714, Level 16, State 6, Line 7
There is already an object named 'ASPStateTempSessions' in the database.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'CreateTempTables'. The stored procedure will still be created.
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 57
The login 'usraspstate' already exists.
'usraspstate' added to role 'db_datareader'.
'usraspstate' added to role 'db_datawriter'.

And here's the script, a tad long....

/*****************************************************************************/
/*This script is called by a Startup Stored Procedure in the Master Database */
/*to enable session state for usraspstate in the aspstate database. */
/*The startup stored procedure is called aspstate_reinstate. */
/*King David 22/06/2004. */
/*****************************************************************************/

/*************************************************************/
/*Drop & destroy the exisiting aspstate database completely. */
/*************************************************************/

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'aspstate')
DROP DATABASE [aspstate]
GO

/****************************************************************************/
/*Create the new database, using the default model, therefore no file names */
/*required or collation required, or individual objects to be destroyed. */
/****************************************************************************/

CREATE DATABASE aspstate
GO

/*******************************/
/*Set the database properties. */
/*******************************/

exec sp_dboption N'aspstate', N'autoclose', N'false'
GO

exec sp_dboption N'aspstate', N'bulkcopy', N'false'
GO

exec sp_dboption N'aspstate', N'trunc. log', N'false'
GO

exec sp_dboption N'aspstate', N'torn page detection', N'true'
GO

exec sp_dboption N'aspstate', N'read only', N'false'
GO

exec sp_dboption N'aspstate', N'dbo use', N'false'
GO

exec sp_dboption N'aspstate', N'single', N'false'
GO

exec sp_dboption N'aspstate', N'autoshrink', N'false'
GO

exec sp_dboption N'aspstate', N'ANSI null default', N'false'
GO

exec sp_dboption N'aspstate', N'recursive triggers', N'false'
GO

exec sp_dboption N'aspstate', N'ANSI nulls', N'false'
GO

exec sp_dboption N'aspstate', N'concat null yields null', N'false'
GO

exec sp_dboption N'aspstate', N'cursor close on commit', N'false'
GO

exec sp_dboption N'aspstate', N'default to local cursor', N'false'
GO

exec sp_dboption N'aspstate', N'quoted identifier', N'false'
GO

exec sp_dboption N'aspstate', N'ANSI warnings', N'false'
GO

exec sp_dboption N'aspstate', N'auto create statistics', N'true'
GO

exec sp_dboption N'aspstate', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'aspstate', N'db chaining', N'false'
GO

/*********************************************************/
/*Ensure all activity is carried out in the aspstate db. */
/*********************************************************/

use aspstate
go

/*********************************/
/* Destroy the user usraspstate. */
/*********************************/

exec sp_droplogin 'usraspstate'
go

exec sp_dropuser 'usraspstate'
go

/**************************/
/*Create the usraspstate usr. */
/**************************/

if not exists (select * from master.dbo.syslogins where loginname = N'usraspstate')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132)

select @logindb = N'aspstate', @loginlang = N'us_english'

if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)

/************************************/
/*Default to the aspstate database. */
/************************************/

select @logindb = N'aspstate'

if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')

select @loginlang = @@language

exec sp_addlogin N'usraspstate', 'usraspstate', @logindb, @loginlang
END
GO

/*************************/
/*Grant database access. */
/*************************/

if not exists (select * from dbo.sysusers where name = N'usraspstate' and uid < 16382)

EXEC sp_grantdbaccess N'usraspstate', N'usraspstate'

GO

/******************************/
/*Grant the role permissions. */
/******************************/

exec sp_addrolemember N'db_datareader', N'usraspstate'
GO

exec sp_addrolemember N'db_datawriter', N'usraspstate'
GO

/*********************************************/
/* Ensure usraspstate is the database owner. */
/*********************************************/

sp_addrolemember 'db_owner','usraspstate'

go

setuser
GO

/***********************************/
/*Add the user defined data types. */
/***********************************/

EXEC sp_addtype N'tAppName', N'varchar (280)', N'not null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tSessionId', N'char (32)', N'not null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tSessionItemLong', N'image', N'null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tSessionItemShort', N'varbinary (7000)', N'null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tTextPtr', N'varbinary (16)', N'null'
GO

setuser
GO

/*****************************************************/
/* create the tempdb table objects. */
/* creation takes place in the usraspstate database. */
/*****************************************************/

CREATE TABLE tempdb..ASPStateTempSessions (
SessionId CHAR(32) NOT NULL PRIMARY KEY,
Created DATETIME NOT NULL DEFAULT GETDATE(),
Expires DATETIME NOT NULL,
LockDate DATETIME NOT NULL,
LockCookie INT NOT NULL,
Timeout INT NOT NULL,
Locked BIT NOT NULL,
SessionItemShort VARBINARY(7000) NULL,
SessionItemLong IMAGE NULL,
)

CREATE TABLE tempdb..ASPStateTempApplications (
AppId INT NOT NULL IDENTITY PRIMARY KEY,
AppName CHAR(280) NOT NULL,
)

CREATE NONCLUSTERED INDEX Index_AppName ON tempdb..ASPStateTempApplications(AppName)

go

CREATE PROCEDURE DeleteExpiredSessions
AS
DECLARE @now DATETIME
SET @now = GETDATE()

DELETE tempdb..aspstateTempSessions
WHERE Expires < @now

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[DeleteExpiredSessions] TO [usraspstate]
GO

CREATE PROCEDURE DropTempTables
AS
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'aspstateTempSessions' AND type = 'U') BEGIN
DROP TABLE tempdb..aspstateTempSessions
END

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'aspstateTempApplications' AND type = 'U') BEGIN
DROP TABLE tempdb..aspstateTempApplications
END

RETURN 0

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[DropTempTables] TO [usraspstate]
GO

CREATE PROCEDURE ResetData
AS
EXECUTE DropTempTables
EXECUTE CreateTempTables
RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[ResetData] TO [usraspstate]
GO

CREATE PROCEDURE TempGetAppId
@appName tAppName,
@appId INT OUTPUT
AS
SELECT @appId = AppId
FROM tempdb..aspstateTempApplications
WHERE AppName = @appName

IF @appId IS NULL BEGIN
INSERT tempdb..aspstateTempApplications
(AppName)
VALUES
(@appName)

SELECT @appId = AppId
FROM tempdb..aspstateTempApplications
WHERE AppName = @appName
END

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempGetAppId] TO [usraspstate]
GO

CREATE PROCEDURE TempGetStateItem
@id tSessionId,
@itemShort tSessionItemShort OUTPUT,
@locked BIT OUTPUT,
@lockDate DATETIME OUTPUT,
@lockCookie INT OUTPUT
AS
DECLARE @textptr AS tTextPtr
DECLARE @length AS INT
DECLARE @now as DATETIME
SET @now = GETDATE()

UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, @now),
@locked = Locked,
@lockDate = LockDate,
@lockCookie = LockCookie,
@itemShort = CASE @locked
WHEN 0 THEN SessionItemShort
ELSE NULL
END,
@textptr = CASE @locked
WHEN 0 THEN TEXTPTR(SessionItemLong)
ELSE NULL
END,
@length = CASE @locked
WHEN 0 THEN DATALENGTH(SessionItemLong)
ELSE NULL
END
WHERE SessionId = @id
IF @length IS NOT NULL BEGIN
READTEXT tempdb..aspstateTempSessions.SessionItemLong @textptr 0 @length
END

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempGetStateItem] TO [usraspstate]
GO

CREATE PROCEDURE TempGetStateItemExclusive
@id tSessionId,
@itemShort tSessionItemShort OUTPUT,
@locked BIT OUTPUT,
@lockDate DATETIME OUTPUT,
@lockCookie INT OUTPUT
AS
DECLARE @textptr AS tTextPtr
DECLARE @length AS INT
DECLARE @now as DATETIME

SET @now = GETDATE()
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, @now),
@lockDate = LockDate = CASE Locked
WHEN 0 THEN @now
ELSE LockDate
END,
@lockCookie = LockCookie = CASE Locked
WHEN 0 THEN LockCookie + 1
ELSE LockCookie
END,
@itemShort = CASE Locked
WHEN 0 THEN SessionItemShort
ELSE NULL
END,
@textptr = CASE Locked
WHEN 0 THEN TEXTPTR(SessionItemLong)
ELSE NULL
END,
@length = CASE Locked
WHEN 0 THEN DATALENGTH(SessionItemLong)
ELSE NULL
END,
@locked = Locked,
Locked = 1
WHERE SessionId = @id
IF @length IS NOT NULL BEGIN
READTEXT tempdb..aspstateTempSessions.SessionItemLong @textptr 0 @length
END

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempGetStateItemExclusive] TO [usraspstate]
GO

CREATE PROCEDURE TempInsertStateItemLong
@id tSessionId,
@itemLong tSessionItemLong,
@timeout INT
AS
DECLARE @now as DATETIME
SET @now = GETDATE()

INSERT tempdb..aspstateTempSessions
(SessionId,
SessionItemLong,
Timeout,
Expires,
Locked,
LockDate,
LockCookie)
VALUES
(@id,
@itemLong,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
1)

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempInsertStateItemLong] TO [usraspstate]
GO

CREATE PROCEDURE TempInsertStateItemShort
@id tSessionId,
@itemShort tSessionItemShort,
@timeout INT
AS

DECLARE @now as DATETIME
SET @now = GETDATE()

INSERT tempdb..aspstateTempSessions
(SessionId,
SessionItemShort,
Timeout,
Expires,
Locked,
LockDate,
LockCookie)
VALUES
(@id,
@itemShort,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
1)

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempInsertStateItemShort] TO [usraspstate]
GO

CREATE PROCEDURE TempReleaseStateItemExclusive
@id tSessionId,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempReleaseStateItemExclusive] TO [usraspstate]
GO

CREATE PROCEDURE TempRemoveStateItem
@id tSessionId,
@lockCookie INT
AS
DELETE tempdb..aspstateTempSessions
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempRemoveStateItem] TO [usraspstate]
GO

CREATE PROCEDURE TempResetTimeout
@id tSessionId
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE())
WHERE SessionId = @id
RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempResetTimeout] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemLong
@id tSessionId,
@itemLong tSessionItemLong,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemLong = @itemLong,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemLong] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemLongNullShort
@id tSessionId,
@itemLong tSessionItemLong,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemLong = @itemLong,
SessionItemShort = NULL,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemLongNullShort] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemShort
@id tSessionId,
@itemShort tSessionItemShort,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemShort = @itemShort,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemShort] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemShortNullLong
@id tSessionId,
@itemShort tSessionItemShort,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemShort = @itemShort,
SessionItemLong = NULL,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemShortNullLong] TO [usraspstate]
GO

/********************************************************/
/* Grant usraspstate permissions on the tempdb objects. */
/********************************************************/

use tempdb
go

if not exists (select * from dbo.sysusers where name = N'usraspstate' and uid < 16382)
EXEC sp_grantdbaccess N'usraspstate', N'usraspstate'
GO

exec sp_addlogin 'usraspstate','usraspstate'
GO

exec sp_addrolemember N'db_datareader', N'usraspstate'
GO

exec sp_addrolemember N'db_datawriter', N'usraspstate'
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempApplications] TO [usraspstate]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempSessions] TO [usraspstate]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempApplications] TO [public]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempSessions] TO [public]
GO

/************************************/
/*Now resize the .MDF & .LDF Files. */
/************************************/

USE master
GO

ALTER DATABASE aspstate
MODIFY FILE
(NAME = 'aspstate',SIZE = 20MB)
GO

ALTER DATABASE aspstate
MODIFY FILE
(NAME = 'aspstate_log',SIZE = 5MB)
GO

/*******/
/*DONE */
/*******/

Hi, can anyone tell me how the errors can be fixed on this script??

Errors:

The CREATE DATABASE process is allocating 0.75 MB on disk 'aspstate'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'aspstate_log'.
Server: Msg 15175, Level 16, State 1, Procedure sp_droplogin, Line 93
Login 'usraspstate' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login.

Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
User 'usraspstate' does not exist in the current database.
Granted database access to 'usraspstate'.
'usraspstate' added to role 'db_datareader'.
'usraspstate' added to role 'db_datawriter'.
'usraspstate' added to role 'db_owner'.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.
Server: Msg 2714, Level 16, State 6, Line 7
There is already an object named 'ASPStateTempSessions' in the database.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'CreateTempTables'. The stored procedure will still be created.
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 57
The login 'usraspstate' already exists.
'usraspstate' added to role 'db_datareader'.
'usraspstate' added to role 'db_datawriter'.

And here's the script, a tad long....

/*****************************************************************************/
/*This script is called by a Startup Stored Procedure in the Master Database */
/*to enable session state for usraspstate in the aspstate database. */
/*The startup stored procedure is called aspstate_reinstate. */
/*King David 22/06/2004. */
/*****************************************************************************/

/*************************************************************/
/*Drop & destroy the exisiting aspstate database completely. */
/*************************************************************/

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'aspstate')
DROP DATABASE [aspstate]
GO

/****************************************************************************/
/*Create the new database, using the default model, therefore no file names */
/*required or collation required, or individual objects to be destroyed. */
/****************************************************************************/

CREATE DATABASE aspstate
GO

/*******************************/
/*Set the database properties. */
/*******************************/

exec sp_dboption N'aspstate', N'autoclose', N'false'
GO

exec sp_dboption N'aspstate', N'bulkcopy', N'false'
GO

exec sp_dboption N'aspstate', N'trunc. log', N'false'
GO

exec sp_dboption N'aspstate', N'torn page detection', N'true'
GO

exec sp_dboption N'aspstate', N'read only', N'false'
GO

exec sp_dboption N'aspstate', N'dbo use', N'false'
GO

exec sp_dboption N'aspstate', N'single', N'false'
GO

exec sp_dboption N'aspstate', N'autoshrink', N'false'
GO

exec sp_dboption N'aspstate', N'ANSI null default', N'false'
GO

exec sp_dboption N'aspstate', N'recursive triggers', N'false'
GO

exec sp_dboption N'aspstate', N'ANSI nulls', N'false'
GO

exec sp_dboption N'aspstate', N'concat null yields null', N'false'
GO

exec sp_dboption N'aspstate', N'cursor close on commit', N'false'
GO

exec sp_dboption N'aspstate', N'default to local cursor', N'false'
GO

exec sp_dboption N'aspstate', N'quoted identifier', N'false'
GO

exec sp_dboption N'aspstate', N'ANSI warnings', N'false'
GO

exec sp_dboption N'aspstate', N'auto create statistics', N'true'
GO

exec sp_dboption N'aspstate', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'aspstate', N'db chaining', N'false'
GO

/*********************************************************/
/*Ensure all activity is carried out in the aspstate db. */
/*********************************************************/

use aspstate
go

/*********************************/
/* Destroy the user usraspstate. */
/*********************************/

exec sp_droplogin 'usraspstate'
go

exec sp_dropuser 'usraspstate'
go

/**************************/
/*Create the usraspstate usr. */
/**************************/

if not exists (select * from master.dbo.syslogins where loginname = N'usraspstate')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132)

select @logindb = N'aspstate', @loginlang = N'us_english'

if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)

/************************************/
/*Default to the aspstate database. */
/************************************/

select @logindb = N'aspstate'

if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')

select @loginlang = @@language

exec sp_addlogin N'usraspstate', 'usraspstate', @logindb, @loginlang
END
GO

/*************************/
/*Grant database access. */
/*************************/

if not exists (select * from dbo.sysusers where name = N'usraspstate' and uid < 16382)

EXEC sp_grantdbaccess N'usraspstate', N'usraspstate'

GO

/******************************/
/*Grant the role permissions. */
/******************************/

exec sp_addrolemember N'db_datareader', N'usraspstate'
GO

exec sp_addrolemember N'db_datawriter', N'usraspstate'
GO

/*********************************************/
/* Ensure usraspstate is the database owner. */
/*********************************************/

sp_addrolemember 'db_owner','usraspstate'

go

setuser
GO

/***********************************/
/*Add the user defined data types. */
/***********************************/

EXEC sp_addtype N'tAppName', N'varchar (280)', N'not null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tSessionId', N'char (32)', N'not null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tSessionItemLong', N'image', N'null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tSessionItemShort', N'varbinary (7000)', N'null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tTextPtr', N'varbinary (16)', N'null'
GO

setuser
GO

/*****************************************************/
/* create the tempdb table objects. */
/* creation takes place in the usraspstate database. */
/*****************************************************/

CREATE TABLE tempdb..ASPStateTempSessions (
SessionId CHAR(32) NOT NULL PRIMARY KEY,
Created DATETIME NOT NULL DEFAULT GETDATE(),
Expires DATETIME NOT NULL,
LockDate DATETIME NOT NULL,
LockCookie INT NOT NULL,
Timeout INT NOT NULL,
Locked BIT NOT NULL,
SessionItemShort VARBINARY(7000) NULL,
SessionItemLong IMAGE NULL,
)

CREATE TABLE tempdb..ASPStateTempApplications (
AppId INT NOT NULL IDENTITY PRIMARY KEY,
AppName CHAR(280) NOT NULL,
)

CREATE NONCLUSTERED INDEX Index_AppName ON tempdb..ASPStateTempApplications(AppName)

go

CREATE PROCEDURE DeleteExpiredSessions
AS
DECLARE @now DATETIME
SET @now = GETDATE()

DELETE tempdb..aspstateTempSessions
WHERE Expires < @now

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[DeleteExpiredSessions] TO [usraspstate]
GO

CREATE PROCEDURE DropTempTables
AS
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'aspstateTempSessions' AND type = 'U') BEGIN
DROP TABLE tempdb..aspstateTempSessions
END

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'aspstateTempApplications' AND type = 'U') BEGIN
DROP TABLE tempdb..aspstateTempApplications
END

RETURN 0

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[DropTempTables] TO [usraspstate]
GO

CREATE PROCEDURE ResetData
AS
EXECUTE DropTempTables
EXECUTE CreateTempTables
RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[ResetData] TO [usraspstate]
GO

CREATE PROCEDURE TempGetAppId
@appName tAppName,
@appId INT OUTPUT
AS
SELECT @appId = AppId
FROM tempdb..aspstateTempApplications
WHERE AppName = @appName

IF @appId IS NULL BEGIN
INSERT tempdb..aspstateTempApplications
(AppName)
VALUES
(@appName)

SELECT @appId = AppId
FROM tempdb..aspstateTempApplications
WHERE AppName = @appName
END

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempGetAppId] TO [usraspstate]
GO

CREATE PROCEDURE TempGetStateItem
@id tSessionId,
@itemShort tSessionItemShort OUTPUT,
@locked BIT OUTPUT,
@lockDate DATETIME OUTPUT,
@lockCookie INT OUTPUT
AS
DECLARE @textptr AS tTextPtr
DECLARE @length AS INT
DECLARE @now as DATETIME
SET @now = GETDATE()

UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, @now),
@locked = Locked,
@lockDate = LockDate,
@lockCookie = LockCookie,
@itemShort = CASE @locked
WHEN 0 THEN SessionItemShort
ELSE NULL
END,
@textptr = CASE @locked
WHEN 0 THEN TEXTPTR(SessionItemLong)
ELSE NULL
END,
@length = CASE @locked
WHEN 0 THEN DATALENGTH(SessionItemLong)
ELSE NULL
END
WHERE SessionId = @id
IF @length IS NOT NULL BEGIN
READTEXT tempdb..aspstateTempSessions.SessionItemLong @textptr 0 @length
END

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempGetStateItem] TO [usraspstate]
GO

CREATE PROCEDURE TempGetStateItemExclusive
@id tSessionId,
@itemShort tSessionItemShort OUTPUT,
@locked BIT OUTPUT,
@lockDate DATETIME OUTPUT,
@lockCookie INT OUTPUT
AS
DECLARE @textptr AS tTextPtr
DECLARE @length AS INT
DECLARE @now as DATETIME

SET @now = GETDATE()
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, @now),
@lockDate = LockDate = CASE Locked
WHEN 0 THEN @now
ELSE LockDate
END,
@lockCookie = LockCookie = CASE Locked
WHEN 0 THEN LockCookie + 1
ELSE LockCookie
END,
@itemShort = CASE Locked
WHEN 0 THEN SessionItemShort
ELSE NULL
END,
@textptr = CASE Locked
WHEN 0 THEN TEXTPTR(SessionItemLong)
ELSE NULL
END,
@length = CASE Locked
WHEN 0 THEN DATALENGTH(SessionItemLong)
ELSE NULL
END,
@locked = Locked,
Locked = 1
WHERE SessionId = @id
IF @length IS NOT NULL BEGIN
READTEXT tempdb..aspstateTempSessions.SessionItemLong @textptr 0 @length
END

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempGetStateItemExclusive] TO [usraspstate]
GO

CREATE PROCEDURE TempInsertStateItemLong
@id tSessionId,
@itemLong tSessionItemLong,
@timeout INT
AS
DECLARE @now as DATETIME
SET @now = GETDATE()

INSERT tempdb..aspstateTempSessions
(SessionId,
SessionItemLong,
Timeout,
Expires,
Locked,
LockDate,
LockCookie)
VALUES
(@id,
@itemLong,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
1)

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempInsertStateItemLong] TO [usraspstate]
GO

CREATE PROCEDURE TempInsertStateItemShort
@id tSessionId,
@itemShort tSessionItemShort,
@timeout INT
AS

DECLARE @now as DATETIME
SET @now = GETDATE()

INSERT tempdb..aspstateTempSessions
(SessionId,
SessionItemShort,
Timeout,
Expires,
Locked,
LockDate,
LockCookie)
VALUES
(@id,
@itemShort,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
1)

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempInsertStateItemShort] TO [usraspstate]
GO

CREATE PROCEDURE TempReleaseStateItemExclusive
@id tSessionId,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempReleaseStateItemExclusive] TO [usraspstate]
GO

CREATE PROCEDURE TempRemoveStateItem
@id tSessionId,
@lockCookie INT
AS
DELETE tempdb..aspstateTempSessions
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempRemoveStateItem] TO [usraspstate]
GO

CREATE PROCEDURE TempResetTimeout
@id tSessionId
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE())
WHERE SessionId = @id
RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempResetTimeout] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemLong
@id tSessionId,
@itemLong tSessionItemLong,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemLong = @itemLong,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemLong] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemLongNullShort
@id tSessionId,
@itemLong tSessionItemLong,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemLong = @itemLong,
SessionItemShort = NULL,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemLongNullShort] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemShort
@id tSessionId,
@itemShort tSessionItemShort,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemShort = @itemShort,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemShort] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemShortNullLong
@id tSessionId,
@itemShort tSessionItemShort,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemShort = @itemShort,
SessionItemLong = NULL,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemShortNullLong] TO [usraspstate]
GO

/********************************************************/
/* Grant usraspstate permissions on the tempdb objects. */
/********************************************************/

use tempdb
go

if not exists (select * from dbo.sysusers where name = N'usraspstate' and uid < 16382)
EXEC sp_grantdbaccess N'usraspstate', N'usraspstate'
GO

exec sp_addlogin 'usraspstate','usraspstate'
GO

exec sp_addrolemember N'db_datareader', N'usraspstate'
GO

exec sp_addrolemember N'db_datawriter', N'usraspstate'
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempApplications] TO [usraspstate]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempSessions] TO [usraspstate]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempApplications] TO [public]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempSessions] TO [public]
GO

/************************************/
/*Now resize the .MDF & .LDF Files. */
/************************************/

USE master
GO

ALTER DATABASE aspstate
MODIFY FILE
(NAME = 'aspstate',SIZE = 20MB)
GO

ALTER DATABASE aspstate
MODIFY FILE
(NAME = 'aspstate_log',SIZE = 5MB)
GO

/*******/
Hi, can anyone tell me how the errors can be fixed on this script??

Errors:

The CREATE DATABASE process is allocating 0.75 MB on disk 'aspstate'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'aspstate_log'.
Server: Msg 15175, Level 16, State 1, Procedure sp_droplogin, Line 93
Login 'usraspstate' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login.

Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
User 'usraspstate' does not exist in the current database.
Granted database access to 'usraspstate'.
'usraspstate' added to role 'db_datareader'.
'usraspstate' added to role 'db_datawriter'.
'usraspstate' added to role 'db_owner'.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.

(1 row(s) affected)

Type added.
Server: Msg 2714, Level 16, State 6, Line 7
There is already an object named 'ASPStateTempSessions' in the database.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'CreateTempTables'. The stored procedure will still be created.
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 57
The login 'usraspstate' already exists.
'usraspstate' added to role 'db_datareader'.
'usraspstate' added to role 'db_datawriter'.

And here's the script, a tad long....

/*****************************************************************************/
/*This script is called by a Startup Stored Procedure in the Master Database */
/*to enable session state for usraspstate in the aspstate database. */
/*The startup stored procedure is called aspstate_reinstate. */
/*King David 22/06/2004. */
/*****************************************************************************/

/*************************************************************/
/*Drop & destroy the exisiting aspstate database completely. */
/*************************************************************/

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'aspstate')
DROP DATABASE [aspstate]
GO

/****************************************************************************/
/*Create the new database, using the default model, therefore no file names */
/*required or collation required, or individual objects to be destroyed. */
/****************************************************************************/

CREATE DATABASE aspstate
GO

/*******************************/
/*Set the database properties. */
/*******************************/

exec sp_dboption N'aspstate', N'autoclose', N'false'
GO

exec sp_dboption N'aspstate', N'bulkcopy', N'false'
GO

exec sp_dboption N'aspstate', N'trunc. log', N'false'
GO

exec sp_dboption N'aspstate', N'torn page detection', N'true'
GO

exec sp_dboption N'aspstate', N'read only', N'false'
GO

exec sp_dboption N'aspstate', N'dbo use', N'false'
GO

exec sp_dboption N'aspstate', N'single', N'false'
GO

exec sp_dboption N'aspstate', N'autoshrink', N'false'
GO

exec sp_dboption N'aspstate', N'ANSI null default', N'false'
GO

exec sp_dboption N'aspstate', N'recursive triggers', N'false'
GO

exec sp_dboption N'aspstate', N'ANSI nulls', N'false'
GO

exec sp_dboption N'aspstate', N'concat null yields null', N'false'
GO

exec sp_dboption N'aspstate', N'cursor close on commit', N'false'
GO

exec sp_dboption N'aspstate', N'default to local cursor', N'false'
GO

exec sp_dboption N'aspstate', N'quoted identifier', N'false'
GO

exec sp_dboption N'aspstate', N'ANSI warnings', N'false'
GO

exec sp_dboption N'aspstate', N'auto create statistics', N'true'
GO

exec sp_dboption N'aspstate', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'aspstate', N'db chaining', N'false'
GO

/*********************************************************/
/*Ensure all activity is carried out in the aspstate db. */
/*********************************************************/

use aspstate
go

/*********************************/
/* Destroy the user usraspstate. */
/*********************************/

exec sp_droplogin 'usraspstate'
go

exec sp_dropuser 'usraspstate'
go

/**************************/
/*Create the usraspstate usr. */
/**************************/

if not exists (select * from master.dbo.syslogins where loginname = N'usraspstate')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132)

select @logindb = N'aspstate', @loginlang = N'us_english'

if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)

/************************************/
/*Default to the aspstate database. */
/************************************/

select @logindb = N'aspstate'

if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')

select @loginlang = @@language

exec sp_addlogin N'usraspstate', 'usraspstate', @logindb, @loginlang
END
GO

/*************************/
/*Grant database access. */
/*************************/

if not exists (select * from dbo.sysusers where name = N'usraspstate' and uid < 16382)

EXEC sp_grantdbaccess N'usraspstate', N'usraspstate'

GO

/******************************/
/*Grant the role permissions. */
/******************************/

exec sp_addrolemember N'db_datareader', N'usraspstate'
GO

exec sp_addrolemember N'db_datawriter', N'usraspstate'
GO

/*********************************************/
/* Ensure usraspstate is the database owner. */
/*********************************************/

sp_addrolemember 'db_owner','usraspstate'

go

setuser
GO

/***********************************/
/*Add the user defined data types. */
/***********************************/

EXEC sp_addtype N'tAppName', N'varchar (280)', N'not null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tSessionId', N'char (32)', N'not null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tSessionItemLong', N'image', N'null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tSessionItemShort', N'varbinary (7000)', N'null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'tTextPtr', N'varbinary (16)', N'null'
GO

setuser
GO

/*****************************************************/
/* create the tempdb table objects. */
/* creation takes place in the usraspstate database. */
/*****************************************************/

CREATE TABLE tempdb..ASPStateTempSessions (
SessionId CHAR(32) NOT NULL PRIMARY KEY,
Created DATETIME NOT NULL DEFAULT GETDATE(),
Expires DATETIME NOT NULL,
LockDate DATETIME NOT NULL,
LockCookie INT NOT NULL,
Timeout INT NOT NULL,
Locked BIT NOT NULL,
SessionItemShort VARBINARY(7000) NULL,
SessionItemLong IMAGE NULL,
)

CREATE TABLE tempdb..ASPStateTempApplications (
AppId INT NOT NULL IDENTITY PRIMARY KEY,
AppName CHAR(280) NOT NULL,
)

CREATE NONCLUSTERED INDEX Index_AppName ON tempdb..ASPStateTempApplications(AppName)

go

CREATE PROCEDURE DeleteExpiredSessions
AS
DECLARE @now DATETIME
SET @now = GETDATE()

DELETE tempdb..aspstateTempSessions
WHERE Expires < @now

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[DeleteExpiredSessions] TO [usraspstate]
GO

CREATE PROCEDURE DropTempTables
AS
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'aspstateTempSessions' AND type = 'U') BEGIN
DROP TABLE tempdb..aspstateTempSessions
END

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'aspstateTempApplications' AND type = 'U') BEGIN
DROP TABLE tempdb..aspstateTempApplications
END

RETURN 0

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[DropTempTables] TO [usraspstate]
GO

CREATE PROCEDURE ResetData
AS
EXECUTE DropTempTables
EXECUTE CreateTempTables
RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[ResetData] TO [usraspstate]
GO

CREATE PROCEDURE TempGetAppId
@appName tAppName,
@appId INT OUTPUT
AS
SELECT @appId = AppId
FROM tempdb..aspstateTempApplications
WHERE AppName = @appName

IF @appId IS NULL BEGIN
INSERT tempdb..aspstateTempApplications
(AppName)
VALUES
(@appName)

SELECT @appId = AppId
FROM tempdb..aspstateTempApplications
WHERE AppName = @appName
END

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempGetAppId] TO [usraspstate]
GO

CREATE PROCEDURE TempGetStateItem
@id tSessionId,
@itemShort tSessionItemShort OUTPUT,
@locked BIT OUTPUT,
@lockDate DATETIME OUTPUT,
@lockCookie INT OUTPUT
AS
DECLARE @textptr AS tTextPtr
DECLARE @length AS INT
DECLARE @now as DATETIME
SET @now = GETDATE()

UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, @now),
@locked = Locked,
@lockDate = LockDate,
@lockCookie = LockCookie,
@itemShort = CASE @locked
WHEN 0 THEN SessionItemShort
ELSE NULL
END,
@textptr = CASE @locked
WHEN 0 THEN TEXTPTR(SessionItemLong)
ELSE NULL
END,
@length = CASE @locked
WHEN 0 THEN DATALENGTH(SessionItemLong)
ELSE NULL
END
WHERE SessionId = @id
IF @length IS NOT NULL BEGIN
READTEXT tempdb..aspstateTempSessions.SessionItemLong @textptr 0 @length
END

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempGetStateItem] TO [usraspstate]
GO

CREATE PROCEDURE TempGetStateItemExclusive
@id tSessionId,
@itemShort tSessionItemShort OUTPUT,
@locked BIT OUTPUT,
@lockDate DATETIME OUTPUT,
@lockCookie INT OUTPUT
AS
DECLARE @textptr AS tTextPtr
DECLARE @length AS INT
DECLARE @now as DATETIME

SET @now = GETDATE()
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, @now),
@lockDate = LockDate = CASE Locked
WHEN 0 THEN @now
ELSE LockDate
END,
@lockCookie = LockCookie = CASE Locked
WHEN 0 THEN LockCookie + 1
ELSE LockCookie
END,
@itemShort = CASE Locked
WHEN 0 THEN SessionItemShort
ELSE NULL
END,
@textptr = CASE Locked
WHEN 0 THEN TEXTPTR(SessionItemLong)
ELSE NULL
END,
@length = CASE Locked
WHEN 0 THEN DATALENGTH(SessionItemLong)
ELSE NULL
END,
@locked = Locked,
Locked = 1
WHERE SessionId = @id
IF @length IS NOT NULL BEGIN
READTEXT tempdb..aspstateTempSessions.SessionItemLong @textptr 0 @length
END

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempGetStateItemExclusive] TO [usraspstate]
GO

CREATE PROCEDURE TempInsertStateItemLong
@id tSessionId,
@itemLong tSessionItemLong,
@timeout INT
AS
DECLARE @now as DATETIME
SET @now = GETDATE()

INSERT tempdb..aspstateTempSessions
(SessionId,
SessionItemLong,
Timeout,
Expires,
Locked,
LockDate,
LockCookie)
VALUES
(@id,
@itemLong,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
1)

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempInsertStateItemLong] TO [usraspstate]
GO

CREATE PROCEDURE TempInsertStateItemShort
@id tSessionId,
@itemShort tSessionItemShort,
@timeout INT
AS

DECLARE @now as DATETIME
SET @now = GETDATE()

INSERT tempdb..aspstateTempSessions
(SessionId,
SessionItemShort,
Timeout,
Expires,
Locked,
LockDate,
LockCookie)
VALUES
(@id,
@itemShort,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
1)

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempInsertStateItemShort] TO [usraspstate]
GO

CREATE PROCEDURE TempReleaseStateItemExclusive
@id tSessionId,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempReleaseStateItemExclusive] TO [usraspstate]
GO

CREATE PROCEDURE TempRemoveStateItem
@id tSessionId,
@lockCookie INT
AS
DELETE tempdb..aspstateTempSessions
WHERE SessionId = @id AND LockCookie = @lockCookie
RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempRemoveStateItem] TO [usraspstate]
GO

CREATE PROCEDURE TempResetTimeout
@id tSessionId
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE())
WHERE SessionId = @id
RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempResetTimeout] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemLong
@id tSessionId,
@itemLong tSessionItemLong,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemLong = @itemLong,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemLong] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemLongNullShort
@id tSessionId,
@itemLong tSessionItemLong,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemLong = @itemLong,
SessionItemShort = NULL,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemLongNullShort] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemShort
@id tSessionId,
@itemShort tSessionItemShort,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemShort = @itemShort,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemShort] TO [usraspstate]
GO

CREATE PROCEDURE TempUpdateStateItemShortNullLong
@id tSessionId,
@itemShort tSessionItemShort,
@timeout INT,
@lockCookie INT
AS
UPDATE tempdb..aspstateTempSessions
SET Expires = DATEADD(n, Timeout, GETDATE()),
SessionItemShort = @itemShort,
SessionItemLong = NULL,
Timeout = @timeout,
Locked = 0
WHERE SessionId = @id AND LockCookie = @lockCookie

RETURN 0

GO

/**********************************/
/* Grant usraspstate permissions. */
/**********************************/

GRANT EXECUTE ON [dbo].[TempUpdateStateItemShortNullLong] TO [usraspstate]
GO

/********************************************************/
/* Grant usraspstate permissions on the tempdb objects. */
/********************************************************/

use tempdb
go

if not exists (select * from dbo.sysusers where name = N'usraspstate' and uid < 16382)
EXEC sp_grantdbaccess N'usraspstate', N'usraspstate'
GO

exec sp_addlogin 'usraspstate','usraspstate'
GO

exec sp_addrolemember N'db_datareader', N'usraspstate'
GO

exec sp_addrolemember N'db_datawriter', N'usraspstate'
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempApplications] TO [usraspstate]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempSessions] TO [usraspstate]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempApplications] TO [public]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[ASPStateTempSessions] TO [public]
GO

/************************************/
/*Now resize the .MDF & .LDF Files. */
/************************************/

USE master
GO

ALTER DATABASE aspstate
MODIFY FILE
(NAME = 'aspstate',SIZE = 20MB)
GO

ALTER DATABASE aspstate
MODIFY FILE
(NAME = 'aspstate_log',SIZE = 5MB)
GO

/*******/
/*DONE */
/*******/

Kristen
Test

22859 Posts

Posted - 2006-12-20 : 10:40:12

Hmmm ... what's with all the CREATE TABLE stuff in TEMPDB ??

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources