aspnet_regsql problem with creating Session tables in mssql

Question

I'am using session state handling in ASP.NET by MSSQL server with:

sessionState mode="SQLServer"

in web.config. Locally I used aspnet_regsql with no problems but while trying to do same on production server it throws exception at me (* - are masked data):

        C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -S somehostserver.com -U _admin -P _password -ssadd -sstype c -d mydb

        Start adding session state.

        .
        An error occurred during the execution of the SQL file 'InstallSqlState.sql'. Th
        e SQL error number is 229 and the SqlException message is: The EXECUTE permissio
        n 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: *
        Database: [*]
        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'***' + '_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 expect
        ed.'

        EXECUTE msdb.dbo.sp_delete_job @job_name = @jobname

        SQL Exception:
        System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was deni
        ed 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, Boolea
        n breakConnection)
           at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
        , Boolean breakConnection)
           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm
        dHandler, 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, Boolean isInstall, SessionStateType sessionStatetype)

Using SQL scripts causes similar errors. What should I do ?! Hosting owner suggests "make backup on ur machine and place it in production", but This causes another problems... Any suggestions !? Or Iam forced to craft by hand all scripts for tables and procedures ?!

Best regards

Soner Gönül · Accepted Answer · 2011-07-16 22:46:53Z

1

It seems that the user is missing the "RSExecRole" role in the Master and MSDB system databases. Reporting Services uses a predefined database role called "RSExecRole" to grant report server permissions to the report server database.

The "RSExecRole" role is created automatically with the report server database. However, when you move a report server database to a new or different SQL Server Database Engine, must re-create the role in the Master and MSDB system databases.

Here is a link reference to how to create the "RSExecRole":

http://technet.microsoft.com/en-us/library/cc281308.aspx

answered Jul 16, 2011 at 22:46

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jackal Over a year ago

I will check out your suggestion, but I wonder what "Reporting services" got to do with sessions set up by aspnet_regsql

Tejas Over a year ago

This does solve the problem and should be marked as solved

Collectives™ on Stack Overflow

aspnet_regsql problem with creating Session tables in mssql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related