rights

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

Getting Database Rights Assignment Overview – sp_HelpRights on GitHub

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

You have a user database with bunch of users and database roles and other database principla types. You need to have an insight into the rights assignments. You would like to know what rights are assigned to particular database principal.

The above task can be quite tricky as the rights on database objects can be granted or denied directly to particular database principal. However each of the database principal can have rights assignment inherited through database or application roles and there can be even a hierarchy of roles membership.

Getting a clear overview about the rights assignment in such situation is very problematic.

The sp_HelpRights procedure can bring you a clear overview about the rights assignments as it lists all the rights granted/revoked toindividual database principals even thoseinherited through roles hierarchy including the complete inheritance path.

Anyway, do not mess this with effective rights for particular user. This is not aim of the stored procedure.

If you want to see effectiverights for particular user, use SSMS princiapl properties to list those (although even this is not fully accurate as it does not reflect rights assigned through server roles membership and many other).

sp_HelpRights

As mentioned above the sp_HelpRights procedure returns an overview of rights assignments in the database.

You can find full source code of the sp_HelpRights as part of my SQL-Scripts project on GitHub in the RightsManagement folder. The script marks the stored procedure as system object so it can run in the context of the current user database.

Syntax

The syntax for the sp_HelpRights is quite sraighforward:

sp_HelpRights [parameters]

To display help for the stored procedure invoke

sp_HelpRights '?'

Parameters

The stored procedure has only two optional parameters @databases and @principals

@databases

A Comma separated list of the databases for which the rights overview should be generated. Default value NULL means current database.

The parameter suport wildcards and exclusion if [-] is used as first character of the name or wildcard.

Sample value Meaning
% All databases
%,-m% All databases except databases starting with m
DBA, User%, -User1% Dtabase [DBA] and all databases starting with User but not starting with User1
? Print help for the sp_HelpRights

@principals

A comma separated list of database principals forwhich the rights overview should be printed. The default value NULL means all database principals

Again the parameter supports wildcards and the syntax is the same as for the @datbases paramter.

sp_HelpRights Output

Invoking the procedure without any parameters shows an overview for the current database and a sample  output looks like below:

sp_helprights_sample1
sp_HelpRights output overview

The output provides overview about all permissions assignments between database objects and database principals and mentioned at the beginnin of the post.

It lists all database principasl on which the permission has some effect even the permission was not GRANTED/DENIED to it, the original grantee (database principal to which the permission was originally granted), a complete inheritance path and as well the database principal which granted/denied the permission.

Let’s look on the below output:

sp_helprights_sample2
Sample 1

 

In the First line of the Sample 1 we have a right assignment to the DATABASE. the Right is CONNECT and the state is GRANT. The rigth assignment has effect on the windows user DatabasePrincipalName = 'NT SERVICE\ReportServer'. We can see that the original grantee of that permission is also the NT SERVICE\Report Server and this means that the permission ws granted directly to that user. it is also clearly visible in the PermissionInheritancePath column as there is only name of that datbase principal. We can also see that the permission was granted by dbo.

On the second line of Sample 1 we have a different case. We see a GRANT of EXECUTE permission on the dbo.CreateSegmentedChunk stored procdure. We can clearly see, that this permission has effect on the DatabasePrincipalName = 'NT SERVICE\ReportServer'. But we can also clearly see, that this permission was not directly granted to the NT SERVICE\ReportServer,  but insted the original grantee was RSExecRole database role. The PermissionInheritancePath also shows that that permission was granted to the NT SERVICE\ReportServer directly from the RSExecRole database role.

sp_helprights_sample3
Sample 2

 

The Sample2 shows a case where there are multiple levels in the inheritance. Wecan see, that the GRANT of SELECT permission on the user table dbo.TestTable has effect on the TestUser datbase principal. But we can clearly see that this permission was not directly granted to that user, bu theoriginal grantee of that permission is database role DBRoleA.

From the PermissionInheritancePath we can alsosee, that the permission has efect on the TestUser through membership in the DBRoleB database role and that the database role DBRoleB is member of the DBRoleA. Therefore the complete inheritance path is DBRoleA => DBRoleB => TestUser.

Summary

As mentioned through the article as as can be seen in the samples, the output of the stored procedure can bring you a clear insigths into rights assignments in your database environment and can be very helpfull for database rights auditing to get clear view of who has or does not has acces where, even there are multiple levels of complex roles membership.

Anyway, the one looking on the output must be clear, that the output does not provide information about the effective permissions of listed database principals.

Any somments and/or suggestions are welcomed.

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.

Cloning user rights in database

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

update: Check new post SQL Server – Cloning User Rights – updated sp_CloneRights on GitHub

Some times it could happen, that you need to create a new database user, which will have exactly the same right as another existing user.

In ideal scenario, you will have all the necessary rights assigned to a database roles, and then when you create the new user, you simply add the user to appropriate roles to grant all the necessary rights.

This is ideal scenario, which is not always met, especially when you have to manage the server after somebody else, who didn’t used roles for granting rights.

In such scenario a below system stored stored procedure can be very handful.

USE [master]
GO
--============================================
-- Author:      Pavel Pawlowski
-- Created:     2010/04/16
-- Description: Copies rights of old user to new user
--==================================================
CREATE PROCEDURE sp_CloneRights (
    @oldUser sysname, --Old user from which to copy right
    @newUser sysname, --New user to which copy rights
    @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
    @NewLoginName sysname = NULL --When a NewLogin name is provided also a creation of user is part of the final script
)
AS
BEGIN
    SET NOCOUNT ON

    CREATE TABLE #output (
        command nvarchar(4000)
    )

    DECLARE
        @command nvarchar(4000),
        @sql nvarchar(max),
        @dbName nvarchar(128),
        @msg nvarchar(max)

    SELECT
        @sql = N'',
        @dbName = QUOTENAME(DB_NAME())

    IF (NOT EXISTS(SELECT 1 FROM sys.database_principals where name = @oldUser))
    BEGIN
        SET @msg = 'Source user ' + QUOTENAME(@oldUser) + ' doesn''t exists in database ' + @dbName
        RAISERROR(@msg, 11,1)
        RETURN
    END    

    INSERT INTO #output(command)
    SELECT '--Database Context' AS command UNION ALL
    SELECT    'USE' + SPACE(1) + @dbName UNION ALL
    SELECT 'SET XACT_ABORT ON'

    IF (ISNULL(@NewLoginName, '') <> '')
    BEGIN
        SET @sql = N'USE ' + @dbName + N';
        IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @newUser)
        BEGIN
            INSERT INTO #output(command)
            SELECT ''--Create user'' AS command

            INSERT INTO #output(command)
            SELECT
                ''CREATE USER '' + QUOTENAME(@NewUser) + '' FOR LOGIN '' + QUOTENAME(@NewLoginName) +
                    CASE WHEN ISNULL(default_schema_name, '''') <> '''' THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(dp.default_schema_name)
                        ELSE ''''
                    END AS Command
            FROM sys.database_principals dp
            INNER JOIN sys.server_principals sp ON dp.sid = sp.sid
            WHERE dp.name = @OldUser
        END'

        EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname, @NewLoginName sysname', @OldUser = @OldUser, @NewUser = @NewUser, @NewLoginName=@NewLoginName
    END

    INSERT INTO #output(command)
    SELECT    '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser)

    INSERT INTO #output(command)
    SELECT '--Role Memberships' AS command

    SET @sql = N'USE ' + @dbName + N';
    INSERT INTO #output(command)
    SELECT ''EXEC sp_addrolemember @rolename =''
        + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''''''') + '', @membername ='' + SPACE(1) + QUOTENAME(@NewUser, '''''''') AS command
    FROM    sys.database_role_members AS rm
    WHERE    USER_NAME(rm.member_principal_id) = @OldUser
    ORDER BY rm.role_principal_id ASC'

    EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname', @OldUser = @OldUser, @NewUser = @NewUser

    INSERT INTO #output(command)
    SELECT '--Object Level Permissions'

    SET @sql = N'USE ' + @dbName + N';
    INSERT INTO #output(command)
    SELECT    CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END
        + SPACE(1) + perm.permission_name + SPACE(1) + ''ON '' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
        + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name) + '')'' END
        + SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
        + CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END
    FROM    sys.database_permissions AS perm
        INNER JOIN
        sys.objects AS obj
        ON perm.major_id = obj.[object_id]
        INNER JOIN
        sys.database_principals AS usr
        ON perm.grantee_principal_id = usr.principal_id
        LEFT JOIN
        sys.columns AS cl
        ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
    WHERE    usr.name = @OldUser
    ORDER BY perm.permission_name ASC, perm.state_desc ASC'

    EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname', @OldUser = @OldUser, @NewUser = @NewUser

    INSERT INTO #output(command)
    SELECT N'--Database Level Permissions'

    SET @sql = N'USE ' + @dbName + N';
    INSERT INTO #output(command)
    SELECT    CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END
        + SPACE(1) + perm.permission_name + SPACE(1)
        + SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
        + CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END
    FROM    sys.database_permissions AS perm
        INNER JOIN
        sys.database_principals AS usr
        ON perm.grantee_principal_id = usr.principal_id
    WHERE    usr.name = @OldUser
    AND    perm.major_id = 0
    ORDER BY perm.permission_name ASC, perm.state_desc ASC'

    EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname', @OldUser = @OldUser, @NewUser = @NewUser

    DECLARE cr CURSOR FOR
        SELECT command FROM #output

    OPEN cr

    FETCH NEXT FROM cr INTO @command

    SET @sql = ''

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@printOnly IS NOT NULL)
            PRINT @command

        SET @sql = @sql + @command + CHAR(13) + CHAR(10)
        FETCH NEXT FROM cr INTO @command
    END

    CLOSE cr
    DEALLOCATE cr

    IF (@printOnly IS NULL OR @printOnly = 0)
        EXEC (@sql)

    DROP TABLE #output
END
GO
EXECUTE sp_ms_marksystemobject 'dbo.sp_CloneRights'
GO

The stored procedure allows copying all the objects and database rights from the old user to a new one. It also clones roles membership for the user.

If the @NewLoginName is specified then then it also creates the @newUser in the database for the login specified and then copies the rights.

@printOnly specifies whether the script should be printed, executed automatically or both printed and executed automatically.

As the system is marked as system, it executes in the context of the database in which is executed.

It also allows copying rights among database roles. If you specify as @oldUser a database or application role name, then the rights of that role will be copied to the @newUser. Again the @newUser can be a user name or database/application role name.

For example if you have a Integration services installed and invoke a below script

USE [msdb]
GO
EXEC sp_CloneRights 'db_ssisadmin', 'NewUser'

you will receive a below script for assigning rights.

--Database Context
USE [msdb]
SET XACT_ABORT ON
--Cloning permissions from [db_ssisadmin] to [NewUser]
--Role Memberships
--Object Level Permissions
GRANT DELETE ON [dbo].[sysssislog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_get_dtsversion] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_make_dtspackagename] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_add_dtspackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_drop_dtspackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_reassign_dtspackageowner] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_get_dtspackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_addlogentry] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_listpackages] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtspackages] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_listfolders] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_deletepackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_deletefolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_getpackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_getfolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_putpackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_checkexists] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_addfolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_renamefolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_setpackageroles] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_getpackageroles] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtspackage_begin] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtspackage_end] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtsstep_begin] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtsstep_end] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtstask] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtspackagelog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtssteplog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtstasklog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtslog_all] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtspackagelog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtssteplog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtstasklog] TO [NewUser]
GRANT INSERT ON [dbo].[sysssislog] TO [NewUser]
GRANT REFERENCES ON [dbo].[sysssislog] TO [NewUser]
GRANT SELECT ON [dbo].[sysssislog] TO [NewUser]
GRANT UPDATE ON [dbo].[sysssislog] TO [NewUser]
--Database Level Permissions

Hope you will find this script useful and hope it will save you a lot of work when cloning rights.

It saved me several times, when I come to an existing database with several hundreds of tables with rights assigned on the object level and I had to introduce a new user with exactly the same rights as an existing one.

One thing needs to be mentioned for end. The procedure clones only object and database right. It doesn’t clone right for system objects, assemblies etc., but you can easily extend the procedure to cover also this this.

This procedure is inspired by  a script I found in past somewhere on internet.