.NET 2005 session state in sql server
-
I got the following error message while trying to run aspnet_regsql.exe from command prompt. The user I have used to run the command will be the user for aspnetstate database. I have selected a custom database option in the command prompt. My user has got rights in a particular d/b and he has the rights to create a database. I have been searching msdn and google also. I would like to know what are the exact permissions required for a sqlserver login to install session state in sql server and also to use the same. "An error occurred during the execution of the SQL file 'InstallSqlState.sql'. The SQL error number is 229 and the SqlException message is: The EXECUTE permission was denied on the object 'sp_delete_job', database 'msdb', schema 'dbo'.If the job does not exist, an error from msdb.dbo.sp_delete_job is expected. SQL Server: [server] Database: [dbname] SQL file loaded: InstallSqlState.sql Commands failed: /* Drop all tables, startup procedures, stored procedures and types. */ /* Drop the DeleteExpiredSessions_Job */ DECLARE @jobname nvarchar(200) SET @jobname = N'[dbname]' + '_Job_DeleteExpiredSessions' -- Delete the [local] job -- We expected to get an error if the job doesn't exist. PRINT 'If the job does not exist, an error from msdb.dbo.sp_delete_job is expected.' EXECUTE msdb.dbo.sp_delete_job @job_name = @jobname SQL Exception: System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'sp_delete_job', database 'msdb', schema 'dbo'. If the job does not exist, an error from msdb.dbo.sp_delete_job is expected. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at system.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception , Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj ect stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds ParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult res ult, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionSta te, Boolea
-
I got the following error message while trying to run aspnet_regsql.exe from command prompt. The user I have used to run the command will be the user for aspnetstate database. I have selected a custom database option in the command prompt. My user has got rights in a particular d/b and he has the rights to create a database. I have been searching msdn and google also. I would like to know what are the exact permissions required for a sqlserver login to install session state in sql server and also to use the same. "An error occurred during the execution of the SQL file 'InstallSqlState.sql'. The SQL error number is 229 and the SqlException message is: The EXECUTE permission was denied on the object 'sp_delete_job', database 'msdb', schema 'dbo'.If the job does not exist, an error from msdb.dbo.sp_delete_job is expected. SQL Server: [server] Database: [dbname] SQL file loaded: InstallSqlState.sql Commands failed: /* Drop all tables, startup procedures, stored procedures and types. */ /* Drop the DeleteExpiredSessions_Job */ DECLARE @jobname nvarchar(200) SET @jobname = N'[dbname]' + '_Job_DeleteExpiredSessions' -- Delete the [local] job -- We expected to get an error if the job doesn't exist. PRINT 'If the job does not exist, an error from msdb.dbo.sp_delete_job is expected.' EXECUTE msdb.dbo.sp_delete_job @job_name = @jobname SQL Exception: System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'sp_delete_job', database 'msdb', schema 'dbo'. If the job does not exist, an error from msdb.dbo.sp_delete_job is expected. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at system.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception , Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj ect stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds ParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult res ult, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionSta te, Boolea
You should use an account that has execute permission on sp_delete_job stored procedure. If you connect using a sysadmin account to run the script this can make things easy.
Hesham A. Amin My blog