I’ve just made publicly available on GitHub a more advanced version of my original sp_CloneRights stored procedure I’ve posted in posted in past Cloning user rights in database.
The new stored procedure is available in my SQL-Scripts repository on GitHub under in the RightsManagement Folder.
The new stored procedure allows scripting of user rights for single or multiple users, it allows specification of the object classes for which permissions should be scripted and other things.
The stored procedure scripts mark the stored procedure as system stored procedure so when executed it runs in the context of current database.
Running the sp_CloneRights without any parameters will print help for the stored procedure.
Sample usages
Below are some sample usages and outputs
Getting Help
sp_CloneRights
will print for the stored procedure:
PRINT 'sp_CloneRights v0.20 (2015-05-14) (C) 2010-2015 Pavel Pawlowski'
PRINT '==============================================================='
Clones rights and/or group membership for specified user(s)
Usage:
[sp_CloneRights] parameters
Parameters:
@user sysname = NULL - Comma separated list of user names to sciprt rights. Supports wildcards when eg '%' means all users
,@newUser sysname = NULL - New user to which copy rights. If New users is provided, @Old user must return exactly one record
,@scriptClass nvarchar(max) = NULL - Comma separated list of permission classes to script. NULL = ALL
,@printOnly bit = 1 - When 1 then only script is printed on screen, when 0 then also script is executed, when NULL, script is only executed and not printed
- When @newUser is not provided then it is always 1
ScriptClass Description
-------------------------------- -------------------------------------------------------------------------------
ROLES_MEMBERSHIP Scripts roles membership
DATABASE Scripts permissions on Database
SCHEMA Scripts permissions on all schemas
OBJECT Scripts permissions on all schema scoped objects
TABLE Scripts permissions on user tables and/or table columns
SYSTEM_TABLE Scripts permissions on system tables and/or table columns. SYSTEM_TABLE must be explicitly specified
VIEW Scripts permissions on all views/andor view columns
STORED_PROCEDURE Scripts permissions on stored procedrues
SQL_STORED_PROCEDURE Scripts permissions on SQL stored procedrues
CLR_STORED_PROCEDURE Scripts permissions on CLR stored procedrues
EXTENDED_STORED_PROCEDURE Scripts permissions on Extended stored procedrues. EXTENDED_STORED_PROCEDURE must be explicitly specified
FUNCTION Scripts permissions on all functions
SQL_FUNCTION Scripts permissions on all SQL functions
CLR_FUNCTION Scripts permissions on all CLR functions
INLINE_FUNCTION Scripts permissions on all inline table-valued functions
SCALAR_FUNCTION Scripts permissions on all scalar functions
TABLE_VALUED_FUNCTION Scripts permissions on all table-valued functions
SQL_SCALAR_FUNCTION Scripts permissions on all SQL scalar functions
SQL_TABLE_VALUED_FUNCTION Scripts permissions on all SQL table-valued functions
CLR_SCALAR_FUNCTION Scripts permissions on all CLR functions
CLR_TABLE_VALUED_FUNCTION Scripts permissions on all CLR table-valued functions
AGGREGATE_FUNCTION Scripts permissions on all CLR aggregate functions
SYNONYM Scripts permissions on all synonyms
SEQUENCE Scripts permissions on all sequences
DATABASE_PRINCIPAL Scripts permissions on all database principals
ROLE Scripts permissions on all roles
APPLICATION_ROLE Scripts permissions on all application Roles
DATABASE_ROLE Scripts permissions on all database Roles
USER Scripts permissions on all users
WINDOWS_GROUP Scripts permissions on all Windows group users
SQL_USER Scripts permissions on all SQL users
WINDOWS_USER Scripts permissions on all Windows users
CERTIFICATE_MAPPED_USER Scripts permissions on all certificate mapped users
ASYMMETRIC_KEY_MAPPED_USER Scripts permissions on all asymmetric key mapped users
TYPE Scripts permissions on all Types
ASSEMBLY Scripts permissions on all assemblies
XML_SCHEMA_COLLECTION Scripts permissions on all XML schema collections
SERVICE_BROKER Scripts permissions on all service broker related bojects
MESSAGE_TYPE Scripts permissions on all message types
SERVICE_CONTRACT Scripts permissions on all service contracts
SERVICE Scripts permissions on all services
REMOTE_SERVICE_BINDING Scripts permissions on all remote service bindings
ROUTE Scripts permissions on all routes
FULLTEXT Scripts permissions on all Fulltext related objects (catalogs and stoplists)
FULLTEXT_CATALOG Scripts permissions on all fulltext catalogs
FULLTEXT_STOPLIST Scripts permissions on all fulltext stoplists
ENCRYP{TION Scripts permissions on all encryptions related objects
SYMMETRIC_KEY Scripts permissions on all symmetric keys
ASYMMETRIC_KEY Scripts permissions on all asymmetric keys
CERTIFICATE Scripts permissions on all certificates
Clone all rights of all users in the current database
sp_cloneRights '%'
Cloning users rights of the RSExecRole
sp_CloneRights 'RSExecRole'
Executing the above script in newly created Report Server database (standalone – not SharePoint integrated) will produce below output:
PRINT 'sp_CloneRights v0.20 (2015-05-14) (C) 2010-2015 Pavel Pawlowski' PRINT '===============================================================' --Database Context USE [ReportServer] SET XACT_ABORT ON --=================================================================== PRINT N'Cloning permissions from [RSExecRole] to [RSExecRole]' --=================================================================== PRINT N'Clonning Role Memberships' ----------------------------------------------------------- EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'RSExecRole' PRINT N'Clonning permission on user tables' ----------------------------------------------------------- GRANT DELETE ON OBJECT::[dbo].[DBUpgradeHistory] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[DBUpgradeHistory] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[DBUpgradeHistory] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[DBUpgradeHistory] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[DBUpgradeHistory] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[DataSets] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[DataSets] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[DataSets] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[DataSets] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[DataSets] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[ServerUpgradeHistory] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[ServerUpgradeHistory] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ServerUpgradeHistory] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ServerUpgradeHistory] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[ServerUpgradeHistory] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Keys] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Keys] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Keys] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Keys] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Keys] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[History] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[History] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[History] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[History] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[History] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[SubscriptionResults] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[SubscriptionResults] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[SubscriptionResults] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[SubscriptionResults] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[SubscriptionResults] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[ConfigurationInfo] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[ConfigurationInfo] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ConfigurationInfo] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ConfigurationInfo] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[ConfigurationInfo] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Favorites] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Favorites] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Favorites] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Favorites] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Favorites] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Catalog] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Catalog] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Catalog] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Catalog] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Catalog] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[SubscriptionsBeingDeleted] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[SubscriptionsBeingDeleted] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[SubscriptionsBeingDeleted] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[SubscriptionsBeingDeleted] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[SubscriptionsBeingDeleted] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[ModelDrill] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[ModelDrill] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ModelDrill] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ModelDrill] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[ModelDrill] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Segment] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Segment] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Segment] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Segment] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Segment] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[ChunkSegmentMapping] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[ChunkSegmentMapping] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ChunkSegmentMapping] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ChunkSegmentMapping] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[ChunkSegmentMapping] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[ModelPerspective] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[ModelPerspective] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ModelPerspective] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ModelPerspective] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[ModelPerspective] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[CachePolicy] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[CachePolicy] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[CachePolicy] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[CachePolicy] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[CachePolicy] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[SegmentedChunk] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[SegmentedChunk] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[SegmentedChunk] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[SegmentedChunk] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[SegmentedChunk] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Users] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Users] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Users] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Users] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Users] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[ExecutionLogStorage] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[ExecutionLogStorage] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExecutionLogStorage] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExecutionLogStorage] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[ExecutionLogStorage] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[DataSource] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[DataSource] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[DataSource] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[DataSource] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[DataSource] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Policies] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Policies] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Policies] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Policies] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Policies] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[SecData] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[SecData] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[SecData] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[SecData] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[SecData] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Roles] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Roles] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Roles] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Roles] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Roles] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[PolicyUserRole] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[PolicyUserRole] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[PolicyUserRole] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[PolicyUserRole] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[PolicyUserRole] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Event] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Event] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Event] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Event] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Event] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Subscriptions] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Subscriptions] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Subscriptions] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Subscriptions] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Subscriptions] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[ActiveSubscriptions] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[ActiveSubscriptions] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ActiveSubscriptions] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ActiveSubscriptions] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[ActiveSubscriptions] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[SnapshotData] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[SnapshotData] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[SnapshotData] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[SnapshotData] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[SnapshotData] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[ChunkData] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[ChunkData] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ChunkData] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ChunkData] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[ChunkData] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Notifications] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Notifications] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Notifications] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Notifications] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Notifications] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Batch] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Batch] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Batch] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Batch] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Batch] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[Schedule] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[Schedule] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[Schedule] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[Schedule] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[Schedule] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[ReportSchedule] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[ReportSchedule] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ReportSchedule] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ReportSchedule] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[ReportSchedule] TO [RSExecRole] AS [dbo] GRANT DELETE ON OBJECT::[dbo].[RunningJobs] TO [RSExecRole] AS [dbo] GRANT INSERT ON OBJECT::[dbo].[RunningJobs] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[RunningJobs] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[RunningJobs] TO [RSExecRole] AS [dbo] GRANT UPDATE ON OBJECT::[dbo].[RunningJobs] TO [RSExecRole] AS [dbo] PRINT N'Clonning permission on views' ----------------------------------------------------------- GRANT REFERENCES ON OBJECT::[dbo].[ExtendedDataSets] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExtendedDataSets] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExecutionLog3] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExecutionLog3] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExecutionLog] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExecutionLog] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExtendedDataSources] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExtendedDataSources] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExecutionLog2] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExecutionLog2] TO [RSExecRole] AS [dbo] PRINT N'Clonning permission on SQL stored procedures' ----------------------------------------------------------- GRANT EXECUTE ON OBJECT::[dbo].[CreateSegmentedChunk] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetTaskProperties] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ReadChunkSegment] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteTask] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[WriteChunkSegment] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSchedulesReports] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateChunkSegment] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[EnforceCacheLimits] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[IsSegmentedChunk] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddReportSchedule] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ShallowCopyChunk] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteReportSchedule] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeepCopySegment] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSnapShotSchedule] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[RemoveSegmentedMapping] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateTimeBasedSubscriptionSchedule] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[RemoveSegment] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetTimeBasedSubscriptionSchedule] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[MigrateExecutionLog] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddRunningJob] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[TempChunkExists] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[RemoveRunningJob] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateEditSession] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdateRunningJob] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ExtendEditSessionLifetime] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetMyRunningJobs] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanExpiredEditSessions] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ListRunningJobs] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetCacheLastUsed] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanExpiredJobs] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSharePointPathsForUpgrade] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateObject] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSharePointSchedulePathsForUpgrade] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteObject] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FindObjectsNonRecursive] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpgradeSharePointPaths] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FindObjectsRecursive] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpgradeSharePointSchedulePaths] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FindParents] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetDataSets] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FindObjectsByLink] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddDataSet] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetIDPairsByLink] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteDataSets] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetChildrenBeforeDelete] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetDataSetForExecution] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetAllProperties] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetParameters] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetObjectContent] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdateSubscriptionResult] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[LoadForDefinitionCheck] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FindKpiItemsByDataSet] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[LoadForRepublishing] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetAllFavoriteItems] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FindFavoriteableItemsNonRecursive] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[RebindDataSource] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FindFavoriteableItemsRecursive] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[RebindDataSet] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[IsFavoriteItem] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetUserServiceToken] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[RemoveItemFromFavorites] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetUserServiceToken] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddItemToFavorites] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetUserSettings] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetUserSettings] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetCompiledDefinition] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetReportForExecution] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetReportParametersForExecution] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[MoveObject] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetDBVersion] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ObjectExists] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetAllProperties] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FlushCacheByID] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FlushReportFromCache] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetParameters] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetObjectContent] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetLastModified] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetNameById] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddDataSource] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetDataSources] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetKeysForInstallation] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteDataSources] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetAnnouncedKey] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ChangeStateOfDataSource] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AnnounceOrGetKey] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FindItemsByDataSource] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetMachineName] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FindItemsByDataSet] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ListInstallations] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[FindItemsByDataSourceRecursive] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ListSubscriptionIDs] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateRole] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ListInfoForReencryption] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetRoles] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetDatasourceInfoForReencryption] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteRole] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetReencryptedDatasourceInfo] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ReadRoleProperties] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSubscriptionInfoForReencryption] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetRoleProperties] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetReencryptedSubscriptionInfo] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetPoliciesForRole] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetClientSecret] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdatePolicy] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetUserServiceTokenForReencryption] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetPolicy] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetReencryptedUserServiceToken] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetSystemPolicy] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteEncryptedContent] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetModelItemPolicy] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteKey] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdatePolicyPrincipal] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetAllConfigurationInfo] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdatePolicyRole] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetOneConfigurationInfo] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetPolicy] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetConfigurationInfo] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSystemPolicy] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddEvent] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeletePolicy] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteEvent] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateSession] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanEventRecords] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteModelItemPolicy] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddExecutionLogEntry] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteAllModelItemPolicies] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ExpireExecutionLogEntries] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetModelItemInfo] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetUserIDBySid] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetModelDefinition] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetUserIDByName] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddModelPerspective] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetUserID] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteModelPerspectives] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetUserIDWithNoCreate] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetModelsAndPerspectives] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetPrincipalID] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetModelPerspectives] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateSubscription] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DereferenceSessionSnapshot] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeliveryRemovedInactivateSubscription] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetSessionData] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddSubscriptionToBeingDeleted] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[WriteLockSession] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[RemoveSubscriptionFromBeingDeleted] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CheckSessionLock] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteSubscription] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSessionData] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSubscription] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSnapshotFromHistory] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ListSubscriptionsUsingDataSource] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanExpiredSessions] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdateSubscriptionStatus] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanExpiredCache] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdateSubscriptionLastRunInfo] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetSessionCredentials] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdateSubscription] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetSessionParameters] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[InvalidateSubscription] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ClearSessionSnapshot] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanNotificationRecords] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[RemoveReportFromSession] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateSnapShotNotifications] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanBrokenSnapshots] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateDataDrivenNotification] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanOrphanedSnapshots] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateNewActiveSubscription] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetCacheOptions] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdateActiveSubscription] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetCacheOptions] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteActiveSubscription] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddReportToCache] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateCacheUpdateNotifications] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetExecutionOptions] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetCacheSchedule] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetExecutionOptions] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteNotification] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdateSnapshot] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetNotificationAttempt] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateChunkAndGetPointer] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateTimeBasedSubscriptionNotification] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[WriteChunkPortion] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteTimeBasedSubscriptionSchedule] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetChunkPointerAndLength] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ListUsedDeliveryProviders] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetChunkInformation] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddBatchRecord] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ReadChunkPortion] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetBatchRecords] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CopyChunksOfType] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteBatchRecords] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteSnapshotAndChunks] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanBatchRecords] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteOneChunk] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanOrphanedPolicies] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateRdlChunk] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[IncreaseTransientSnapshotRefcount] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeletePersistedStreams] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DecreaseTransientSnapshotRefcount] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteExpiredPersistedStreams] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[MarkSnapshotAsDependentOnUser] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeletePersistedStream] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetSnapshotProcessingFlags] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddPersistedStream] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetSnapshotChunksVersion] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[LockPersistedStream] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[LockSnapshotForUpgrade] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[WriteFirstPortionPersistedStream] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[InsertUnreferencedSnapshot] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[WriteNextPortionPersistedStream] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[PromoteSnapshotInfo] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetFirstPortionPersistedStream] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdateSnapshotPaginationInfo] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetPersistedStreamError] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSnapshotPromotedInfo] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetNextPortionPersistedStream] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[AddHistoryRecord] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSnapshotChunks] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetHistoryLimit] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetDrillthroughReports] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ListHistory] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteDrillthroughReports] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanHistoryForReport] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetDrillthroughReports] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanAllHistories] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetDrillthroughReport] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteHistoryRecord] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetUpgradeItems] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteAllHistoryForReport] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[SetUpgradeItemStatus] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[DeleteHistoriesWithNoPolicy] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetPolicyRoots] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[Get_sqlagent_job_status] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetDataSourceForUpgrade] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateTask] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetSubscriptionsForUpgrade] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdateTask] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[StoreServerParameters] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdateScheduleNextRunTime] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetServerParameters] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ListScheduledReports] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CleanExpiredServerParameters] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ListTasks] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CopyChunks] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ListTasksForMaintenance] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[CreateNewSnapshotVersion] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[ClearScheduleConsistancyFlags] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[UpdateSnapshotReferences] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetAReportsReportAction] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[OpenSegmentedChunk] TO [RSExecRole] AS [dbo] GRANT EXECUTE ON OBJECT::[dbo].[GetTimeBasedSubscriptionReportAction] TO [RSExecRole] AS [dbo] PRINT N'Clonning permission on SQL inline table-valued functions' ----------------------------------------------------------- GRANT REFERENCES ON OBJECT::[dbo].[ExtendedCatalog] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExtendedCatalog] TO [RSExecRole] AS [dbo]
Scripting RSExecRole Rights on Views and SQL In-Line Table Valued Functions
sp_CloneRights @user = 'RSExecRole', @scriptClass='VIEW,SQL_FUNCTION'
This will produce a below script:
PRINT 'sp_CloneRights v0.20 (2015-05-14) (C) 2010-2015 Pavel Pawlowski' PRINT '===============================================================' --Database Context USE [ReportServer] SET XACT_ABORT ON --=================================================================== PRINT N'Cloning permissions from [RSExecRole] to [RSExecRole]' --=================================================================== PRINT N'Clonning permission on views' ----------------------------------------------------------- GRANT REFERENCES ON OBJECT::[dbo].[ExtendedDataSets] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExtendedDataSets] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExecutionLog3] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExecutionLog3] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExecutionLog] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExecutionLog] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExtendedDataSources] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExtendedDataSources] TO [RSExecRole] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExecutionLog2] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExecutionLog2] TO [RSExecRole] AS [dbo] PRINT N'Clonning permission on SQL inline table-valued functions' ----------------------------------------------------------- GRANT REFERENCES ON OBJECT::[dbo].[ExtendedCatalog] TO [RSExecRole] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExtendedCatalog] TO [RSExecRole] AS [dbo]
Generating script for granting rights of RSExecRole Rights on Views and SQL In-Line Table Valued Functions to MyNewUser
sp_CloneRights @user = 'RSExecRole', @newUser='MyNewUser', @scriptClass='VIEW,SQL_FUNCTION'
Produces the requested script with below output:
PRINT 'sp_CloneRights v0.20 (2015-05-14) (C) 2010-2015 Pavel Pawlowski' PRINT '===============================================================' --Database Context USE [ReportServer] SET XACT_ABORT ON --=================================================================== PRINT N'Cloning permissions from [RSExecRole] to [MyNewUser]' --=================================================================== PRINT N'Clonning permission on views' ----------------------------------------------------------- GRANT REFERENCES ON OBJECT::[dbo].[ExtendedDataSets] TO [MyNewUser] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExtendedDataSets] TO [MyNewUser] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExecutionLog3] TO [MyNewUser] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExecutionLog3] TO [MyNewUser] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExecutionLog] TO [MyNewUser] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExecutionLog] TO [MyNewUser] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExtendedDataSources] TO [MyNewUser] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExtendedDataSources] TO [MyNewUser] AS [dbo] GRANT REFERENCES ON OBJECT::[dbo].[ExecutionLog2] TO [MyNewUser] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExecutionLog2] TO [MyNewUser] AS [dbo] PRINT N'Clonning permission on SQL inline table-valued functions' ----------------------------------------------------------- GRANT REFERENCES ON OBJECT::[dbo].[ExtendedCatalog] TO [MyNewUser] AS [dbo] GRANT SELECT ON OBJECT::[dbo].[ExtendedCatalog] TO [MyNewUser] AS [dbo]
Any comments bug reports or suggestions for improvements are welcomed.
Pavel's SQL Pages 
[…] Cloning User Rights […]
Ciao Pavel,
first of all thanks a lot for your efforts, I really appreciate it.
I have just one question for you: I don’t understand, if I want clone user rights for ALL databases in a single SQL instance, how to set the ‘@database’ parameter.
One more time, thanks in advance for your help.
Ciao
Enzo
This is easy. You can specify only certain databases and separating them by commas eg. @database=’DB1,DB2′.
As mentioned in the help, you can use also wildcards for example all databases starting with ‘T’ or ending by ‘S’ @database=’T%,%S’
And finally for all databases you simply put @database=’%’ which matches all databases.
Please download latest version from github at it contains also some bug-fixes.
Hi Pavel,
Great script! Big thank you. I was wondering if it were possible to update it to include some error checking around suspect databases?
SELECT DATABASEPROPERTYEX (N’DemoSuspect’, N’STATUS’) AS N’Status’;
GO
I have a few that look to be created by CommVault backup snapshots but the script keeping tripping up on them. Is it possible to check the status and if suspect continue?
Cheers,
Shane
All good, i just updated to include state not equal to suspect (4) when getting DB list. 🙂
WHERE Left(dn.DBName, 1) <> ‚-‚ AND state <> 4
Thanks again!
[…] article is follow-up of the previous related posts SQL Server – Cloning User Rights – updated sp_CloneRights on GitHub and Cloning user rights […]