Snippet

SQL Server – Cloning User Rights – New Refactored version of sp_CloneRights on GitHub

Pavel Pawlowski SQL Server, T-SQL , , , , , ,

Just published a new updated and completely refactored version of the sp_CloneRights stored procedure.

The new version is available in my SQL Scripts repository on GitHub under the RightsManagement folder.

Procedure internals were completely refactored, added a new option in the to script classes (MS_SHIPPED). With this new script class the procedure now by default does not script permissions for schema bound objects which have set property “is_ms_shipped” – this means an object marked as system object. If you want to script permissions for such objects, you need to specify the MS_SHIPPED explicitly in the script classes list.

New parameters and parameters enhancements

The @scriptClass parameter was further enhanced and now supports wildcards. Example below shows scripting of permissions on all schema-scope objects except CLR objects.

sp_CloneRights 
    @principal = '%'
    ,@scriptClass = 'OBJECT,-%CLR%'

Previous versions added printing of informational messages about the permissions being assigned. current version further improves those information, but also introduces @noInfoMsgs parameter, which when set to 0 does not outputs that into the final script. How ti behaves can be seen in samples below in the New Output format section.

Azure Support

Stored procedure now supports also Azure SQL Database. When deploying procedure to azure SQL Database, remove or comment-out the unsupported USE [master] statement. You can also remove the USE statement in case you can to create the procedure in the user database. By default on SQL Server instance the procedure is created in the master database and marked as system object. Thanks to this it can be executed easily in every database and in case not specifying the @database parameter it runs in the context of current database.

/* *****************************************************************************************
	                                  AZURE SQL DB Notice

   Comment-out the unsupported USE [master] when running in Azure SQL DB/Synapse Analytics
   or ignore error caused by unsupported USE statement
***************************************************************************************** */
USE [master]
GO

New Output format

Output of the function was changed so the script is no longer printed in to messages window of SSMS, but instead the procedure returns the script as a result set, which can be easily copied to target target window.

It is possible to use @noInfoMsg = 1 parameter to remove the printing of the informational messages from the script and keep only the permissions related statements:

Snippet of sample output with @noInfoMsg = 1
Snippet of sample output with @noInfoMsg = 1

Help for the updated function is available in the GitHub Wiki.

This article is follow-up of the previous related posts SQL Server – Cloning User Rights – updated sp_CloneRights on GitHub and Cloning user rights in database.

Advertisement

SQL Server – Cloning User Rights – updated sp_CloneRights on GitHub

Pavel Pawlowski SQL Server, T-SQL , , , , , ,

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.

Code Snippets in SSMS Denali

Pavel Pawlowski SQL Server, SSMS , , , ,

Microsoft has introduced a new version of SQL Server Management Studio in the first CTP1 preview of the next SQL Server version code name Denali. The new SSMS is based on new VS 2010 shell and brings some new features.

One of the new features introduced in the CTP1 are new code snippets. Who was working with third party tools or was developing applications in Visual Studio, for sure knows this feature well.

The SSMS supports two kinds of snippets

  1. Insert snippets (Expansion)
  2. Surround With snippets

You can manage the snippets using the Code Snippets Manager available from Tools menu.

SSMS Denali - Code Snippets Manager

The code snippets are using the standard VS2005 snippet XML format, so you can use current available tools for creating and modifying the SQL Server snippets.

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
    <_locDefault _loc="locNone" />
    <_locTag _loc="locData">Title</_locTag>
    <_locTag _loc="locData">Description</_locTag>
    <_locTag _loc="locData">Author</_locTag>
    <_locTag _loc="locData">ToolTip</_locTag>
</_locDefinition>
    <CodeSnippet Format="1.0.0">
        <Header>
            <Title>Create Inline Table Function</Title>
            <Shortcut></Shortcut>
            <Description>Creates an inline table function.</Description>
            <Author>Microsoft Corporation</Author>
            <SnippetTypes>
                <SnippetType>Expansion</SnippetType>
            </SnippetTypes>
        </Header>
        <Snippet>
            <Declarations>
                                <Literal>
                                    <ID>SchemaName</ID>
                                    <ToolTip>Name of the schema</ToolTip>
                                    <Default>dbo</Default>
                                </Literal>
                                <Literal>
                                    <ID>FunctionName</ID>
                                    <ToolTip>Name of the function</ToolTip>
                                    <Default>FunctionName</Default>
                                </Literal>
                                <Literal>
                                    <ID>Param1</ID>
                                    <ToolTip>Name of the input parameter</ToolTip>
                                    <Default>param1</Default>
                                </Literal>
                                <Literal>
                                    <ID>Datatype_Param1</ID>
                                    <ToolTip>Data type of the input parameter</ToolTip>
                                    <Default>int</Default>
                                </Literal>
                                <Literal>
                                    <ID>Param2</ID>
                                    <ToolTip>Name of the input parameter</ToolTip>
                                    <Default>param2</Default>
                                </Literal>
                                <Literal>
                                    <ID>Datatype_Param2</ID>
                                    <ToolTip>Data type of the input parameter</ToolTip>
                                    <Default>char(5)</Default>
                                </Literal>
            </Declarations>
            <Code Language="SQL">
<![CDATA[CREATE FUNCTION [$SchemaName$].[$FunctionName$](
    @$Param1$ $Datatype_Param1$,
    @$Param2$ $Datatype_Param2$)
RETURNS TABLE AS RETURN(
    SELECT @$Param1$ AS c1,
           @$Param2$ AS c2)]]>
            </Code>
        </Snippet>
    </CodeSnippet>
</CodeSnippets>

Insert (Expansion) snippets

The expansion snippets are similar to the Templates known from previous versions of SSMS and which are also available in new version of SSMS. You can insert the snippet using the menu Edit/ItelliSense/Insert Snippet…, using a default keyboard shortcut Ctrl+K, Ctrl+X, or using the context menu after mouse right click.

SSMS Denali IntelliSense - Isert Snippet

Once you invoke the command you can choose from the available snippets.

SSMS Denali IntelliSense - choosing snippet

The snippet behave differently from the Templates. After the snippet is inserted several replacement parts are highlighted and you can substitute the default values by your own. Then all all occurrences are automatically substituted in the snippet. You can switch among the replacements using the Tab key. Once you are ready with modifications you simply hit the Enter Key.

SSMS Denali - Create Function Snippet

Surround With snippets

In contrast to Insert (Expansion) snippets the Surround with snippets take all the selected text and surround it by the code contained in the snippet. Surround with snippets are again accessible using the menu or Ctrl+K, Ctrl+S default shortcut.

SSMS Denali - Inserting Surround With Snippet

SSMS Denali - Surround With Snippet

Missing snippet functionality

The current snippet implementation in SSMS has only a few possibilities and small power comparing the to snippets in Visual Studio. Hope that it is only by the current CTP status and Microsoft will implement following before the final release of the product.

Snippets shortcuts

As you can see from the image of the Code Snippet Manager above, there is Shortcut written I the details of the snippet. Unfortunately all the snippets have unassigned shortcuts and event you edit the source of the snippet and put some shortcuts, the current version of SSMS doesn’t support the snippet shortcuts. It could be nice to have eg. “itf” shortcut for Inline Table Function and simple press Tab for the snippet to be inserted as it works in Visual Studio

Functions in Snippets

I would welcome a functions element functionality in the SQL snippets. E.g.. Function for expanding table fields from a table name etc. Something like GenerateSwitchCases function for expanding enum members in C#. By adding such functionality it would be very easy to write a easy to use complex snippets e.g.. for CRUD procedures generation etc.. SSMS Denali is Based on VS 2010 and visual studio doesn’t allow writing custom functions for use inside of snippets, at least Microsoft could introduce at least the function for table expansions I mentioned here.