Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have multiple servers and 4 of them have reporting services installed with the ReportServer & ReportServerTempDB databases.

A few days ago I was setting up security for a new login and I found out that the RSExecRole role is missing in my dev server (in the reportserver database). It exists on the ReportServerTempDB, master & msdb databases.

I have found a way to create it on the master & msdb databases on msdn but it didn't help me creating it on reportserver with all the securibles & properties similar to the other environents I run.

Has anyone encountered this problem before? Can anyone help me with a script and an explanation about this role?

share|improve this question
    
Why you can't create it in reportserver database? Do you get an error? –  bojan Mar 8 '13 at 14:15
    
No errors... but it has securables and schema and realationship with the master & msdb that i need to understand instead of just creating a role... –  Shimon Gb Gibraltar Mar 8 '13 at 16:08
add comment

2 Answers 2

up vote 4 down vote accepted
+50

Open "Reporting Services Configuration Manager" --> Database, verify the login under [Current Report Server Database Credential] is one of users in RSExecRole or not, if not, click "Change Credentials" button to change to an user in RSExecRole.

You can also use commnadline utility rsconfig as well http://technet.microsoft.com/en-us/library/ms162837.aspx

Since you wanted the code as well, I have scripted it out for you ....

Edit: I have edited, so that below becomes the full code.

    /****** Object: Schema [RSExecRole] ***/ 

CREATE SCHEMA [RSExecRole] AUTHORIZATION [RSExecRole] 
GO 
/*** Object: DatabaseRole [RSExecRole] ******

/ CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]
 GO 

--Security creation script for role RSExecRole

    --Add Role To Database


    EXEC sp_addrole
        @rolename 'RSExecRole'

    --Set Object Specific Permissions For Role
    GRANT
        EXECUTE
        ON [dbo].[FindItemsByDataSource]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[History]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[FindItemsByDataSet]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[FindItemsByDataSourceRecursive]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetDBVersion]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateRole]
        TO RSExecRole
    GRANT
        SELECT,REFERENCES
        ON [dbo].[ExtendedCatalog]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetRoles]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[ConfigurationInfo]
        TO RSExecRole
    GRANT
        SELECT,REFERENCES
        ON [dbo].[ExtendedDataSources]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteRole]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetKeysForInstallation]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ReadRoleProperties]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[Catalog]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetAnnouncedKey]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetRoleProperties]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AnnounceOrGetKey]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetPoliciesForRole]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetMachineName]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[SubscriptionsBeingDeleted]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdatePolicy]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ListInstallations]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetPolicy]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[ModelDrill]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ListSubscriptionIDs]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[Segment]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetSystemPolicy]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ListInfoForReencryption]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetModelItemPolicy]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetDatasourceInfoForReencryption]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdatePolicyPrincipal]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetReencryptedDatasourceInfo]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[ChunkSegmentMapping]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdatePolicyRole]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[ModelPerspective]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSubscriptionInfoForReencryption]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetPolicy]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetReencryptedSubscriptionInfo]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSystemPolicy]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[CachePolicy]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteEncryptedContent]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeletePolicy]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteKey]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[SegmentedChunk]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateSession]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetAllConfigurationInfo]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteModelItemPolicy]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[Users]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetOneConfigurationInfo]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteAllModelItemPolicies]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetConfigurationInfo]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[ExecutionLogStorage]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetModelItemInfo]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[DataSource]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddEvent]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetModelDefinition]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteEvent]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddModelPerspective]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanEventRecords]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteModelPerspectives]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[Policies]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddExecutionLogEntry]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetModelsAndPerspectives]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ExpireExecutionLogEntries]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetModelPerspectives]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetUserIDBySid]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DereferenceSessionSnapshot]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetUserIDByName]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetSessionData]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[SecData]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetUserID]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[WriteLockSession]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetPrincipalID]
        TO RSExecRole
    GRANT
        SELECT,REFERENCES
        ON [dbo].[ExecutionLog2]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CheckSessionLock]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[Roles]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateSubscription]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSessionData]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeliveryRemovedInactivateSubscription]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSnapshotFromHistory]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[PolicyUserRole]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddSubscriptionToBeingDeleted]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanExpiredSessions]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[RemoveSubscriptionFromBeingDeleted]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanExpiredCache]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteSubscription]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetSessionCredentials]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSubscription]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetSessionParameters]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ListSubscriptionsUsingDataSource]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ClearSessionSnapshot]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdateSubscriptionStatus]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[RemoveReportFromSession]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdateSubscriptionLastRunInfo]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanBrokenSnapshots]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdateSubscription]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanOrphanedSnapshots]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[InvalidateSubscription]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetCacheOptions]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanNotificationRecords]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetCacheOptions]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateSnapShotNotifications]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddReportToCache]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateDataDrivenNotification]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetExecutionOptions]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[Event]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateNewActiveSubscription]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetExecutionOptions]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdateActiveSubscription]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdateSnapshot]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteActiveSubscription]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateChunkAndGetPointer]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[Subscriptions]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateCacheUpdateNotifications]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[WriteChunkPortion]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetCacheSchedule]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetChunkPointerAndLength]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteNotification]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetChunkInformation]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetNotificationAttempt]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ReadChunkPortion]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateTimeBasedSubscriptionNotification]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CopyChunksOfType]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[ActiveSubscriptions]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteTimeBasedSubscriptionSchedule]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteSnapshotAndChunks]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ListUsedDeliveryProviders]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteOneChunk]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddBatchRecord]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateRdlChunk]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[SnapshotData]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetBatchRecords]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeletePersistedStreams]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteBatchRecords]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteExpiredPersistedStreams]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[ChunkData]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanBatchRecords]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeletePersistedStream]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanOrphanedPolicies]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddPersistedStream]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[IncreaseTransientSnapshotRefcount]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[LockPersistedStream]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DecreaseTransientSnapshotRefcount]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[WriteFirstPortionPersistedStream]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[Notifications]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[MarkSnapshotAsDependentOnUser]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[WriteNextPortionPersistedStream]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetSnapshotProcessingFlags]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetFirstPortionPersistedStream]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetSnapshotChunksVersion]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetPersistedStreamError]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[Batch]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[LockSnapshotForUpgrade]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetNextPortionPersistedStream]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[Schedule]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[InsertUnreferencedSnapshot]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSnapshotChunks]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[PromoteSnapshotInfo]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetDrillthroughReports]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdateSnapshotPaginationInfo]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteDrillthroughReports]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSnapshotPromotedInfo]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetDrillthroughReports]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[ReportSchedule]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddHistoryRecord]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetDrillthroughReport]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetHistoryLimit]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetUpgradeItems]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ListHistory]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetUpgradeItemStatus]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanHistoryForReport]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetPolicyRoots]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanAllHistories]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetDataSourceForUpgrade]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteHistoryRecord]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSubscriptionsForUpgrade]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteAllHistoryForReport]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[StoreServerParameters]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[RunningJobs]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteHistoriesWithNoPolicy]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetServerParameters]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[Get_sqlagent_job_status]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanExpiredServerParameters]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateTask]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CopyChunks]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdateTask]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateNewSnapshotVersion]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdateScheduleNextRunTime]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdateSnapshotReferences]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ListScheduledReports]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[OpenSegmentedChunk]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ListTasks]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateSegmentedChunk]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ListTasksForMaintenance]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ReadChunkSegment]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ClearScheduleConsistancyFlags]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[WriteChunkSegment]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetAReportsReportAction]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateChunkSegment]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetTimeBasedSubscriptionReportAction]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[IsSegmentedChunk]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetTaskProperties]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ShallowCopyChunk]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteTask]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeepCopySegment]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSchedulesReports]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[RemoveSegmentedMapping]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[EnforceCacheLimits]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[RemoveSegment]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddReportSchedule]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[MigrateExecutionLog]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteReportSchedule]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[TempChunkExists]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSnapShotSchedule]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateEditSession]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateTimeBasedSubscriptionSchedule]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetTimeBasedSubscriptionSchedule]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanExpiredEditSessions]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddRunningJob]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetCacheLastUsed]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[RemoveRunningJob]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSharePointPathsForUpgrade]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpdateRunningJob]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetSharePointSchedulePathsForUpgrade]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetMyRunningJobs]
        TO RSExecRole
    GRANT
        SELECT,REFERENCES
        ON [dbo].[ExtendedDataSets]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ListRunningJobs]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpgradeSharePointPaths]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CleanExpiredJobs]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[UpgradeSharePointSchedulePaths]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[CreateObject]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetDataSets]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteObject]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddDataSet]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[FindObjectsNonRecursive]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteDataSets]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[FindObjectsRecursive]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetDataSetForExecution]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[FindParents]
        TO RSExecRole
    GRANT
        SELECT,REFERENCES
        ON [dbo].[ExecutionLog3]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[FindObjectsByLink]
        TO RSExecRole
    GRANT
        SELECT,REFERENCES
        ON [dbo].[ExecutionLog]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetIDPairsByLink]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetChildrenBeforeDelete]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetAllProperties]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetParameters]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetObjectContent]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[LoadForDefinitionCheck]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[LoadForRepublishing]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[RebindDataSource]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[RebindDataSet]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetCompiledDefinition]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetReportForExecution]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetReportParametersForExecution]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[DBUpgradeHistory]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[MoveObject]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ObjectExists]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetAllProperties]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[FlushCacheByID]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[FlushReportFromCache]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetParameters]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetObjectContent]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[DataSets]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[SetLastModified]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetNameById]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[AddDataSource]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[GetDataSources]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[DeleteDataSources]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[Keys]
        TO RSExecRole
    GRANT
        SELECT,INSERT,UPDATE,DELETE,REFERENCES
        ON [dbo].[ServerUpgradeHistory]
        TO RSExecRole
    GRANT
        EXECUTE
        ON [dbo].[ChangeStateOfDataSource]
        TO RSExecRole
    GO
share|improve this answer
    
it does no create the RSExecRole Schema that exists in other servers and owned by this role and i need an explanation about this role and is importance to the system because it seems that rs is still working just fine without it, therefore its a good but a partial answer –  Shimon Gb Gibraltar Mar 13 '13 at 6:44
    
Below is the tsql for RS schema and role: /****** Object: Schema [RSExecRole] ***/ CREATE SCHEMA [RSExecRole] AUTHORIZATION [RSExecRole] GO /*** Object: DatabaseRole [RSExecRole] ******/ CREATE ROLE [RSExecRole] AUTHORIZATION [dbo] GO –  Kin Mar 13 '13 at 13:45
    
Exp: The Reporting Services Configuration tool will assign the account or database user to the Public and RSExecRole roles for the report server databases. The RSExecRole provides permissions for accessing the database tables and for executing stored procedures. The RSExecRole is created in master and msdb when you create the report server database. The RSExecRole is a member of the db_owner role for the report server databases, allowing the report server to update its own schema in support of an auto-upgrade process. Ref:technet.microsoft.com/en-us/library/… –  Kin Mar 13 '13 at 13:49
    
thank's kin, if you could just edit your last comments into a your answer to create a complete answer for other people who will come across this question it will be great, make this a full answer and you will get the bounty –  Shimon Gb Gibraltar Mar 14 '13 at 5:52
    
I have edited my answer. Since I am new to this site, don't know the norms of it .. Apologies for that ... and Glad that the answer helped you .. –  Kin Mar 14 '13 at 14:07
add comment
  • Can anyone help me with a script?

Sure! Run SQL Profiler, create new ReportServer database using 'Reporting Services Configuration Manager' (you can specify your own name to avoid collisions) and capture all statements. Final script depends on sql version and edition so it's not generic and shouldn't be reused. It's good for education (e.g. RSExecRole dependencies) and nothing else.

  • Has anyone encountered this problem before?

I've seen attempts of scripting out all ReportServer objects. It can sound good, but it's bad idea - too easy to miss something important (RSExecRole). 3rd party databases (Reporting Services, ASPState etc) should be created and configured with dedicated tools. Another explanation - somebody explicitly dropped that role.

share|improve this answer
    
thanks but the answer is not full and returns me to where i stand, i cant risk creating something based in profiling the installation and the RSExecRole is non scriptable (is does but without the objects and securables...) –  Shimon Gb Gibraltar Mar 12 '13 at 8:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.