Clone / Copy SSIS Catalog Project Configuration – sp_SSISCloneConfiguration GitHub

When you deploy a SSIS project to SSIS Catalog, you have the possibility to Configure the deployed SSIS project so you do not need to pass the parameter execution values each time you are executing the the project packages. You can configure the project to use explicitly provided value or you can use reference to Environment Variables.

This is very useful feature, however if you are using multiple environments, like DEV, TEST, QA, PROD, then you have to handle that configuration in each of the environments. To simplify the process of setting up the parameter, you can scrip the configuration.

The pitfall of scripting the configuration is, that you can do that automatically only during initial setup of the configuration. Once you confirm the configuration values in SSMS, there is no possibility to script-out any existing configuration.

sp_SSISCloneConfiguration

sp_SSISCloneConfiguration is a custom stored procedure which takes care about cloning/copying the configurations among different projects in the same SSISDB catalog as well as it provides functionality to generate reusable scripts to easily re-apply the configurations even among different SSIS Servers.

The stored procedure also takes care about sensitive values re-encryption when those are being cloned as SSISS is using different encryption keys for each project in the SSIS catalog, so simple copy of the encrypted values does not work among different projects.

The stored procedure executes in the context of the AllSchemaOwner database user in the SSISDB, which ensure access to all areas of SSISDB including the needed certificates and symmetric keys to allow scripting. The actual re-application of the configuration script is running in the context of the caller as the Internal SSIS catalog stored procedures for configuring projects needs to be executed in the context of windows credentials.

The create scripts grants EXECUTE permission to the sssis_admin database role in the SSISDB catalog so the SSSIS admins can utilize that stored procedure.

Be careful, anyone with EXECUTE permission on the stored procedure is capable of seeing decrypted sensitive values when printing the script. The script by default grants that permission to ssis_admin database role. If you want to keep that functionality only to sysadmins, remove the GRANT at the end of the script.

The complete source code for the sp_SSISCloneConfiguration is available in my SQL-Scripts project on GitHub in the SSISDB folder.

The syntax of the stored procedure is as follow:

sp_SSISCloneConfiguration
    @sourceFolder             = 'Source Folder Name'
   ,@sourceProject            = 'Source Project Name'
   ,@sourceObject             = 'Optional Source Object Name'
   ,@destinationFolder        = 'Optional Destination Folder Name'
   ,@destinationProject       = 'Optional Destination Project Name'
   ,@printScript              = 1 --Specifies whether only script should be printed
   ,@decryptSensitiveInScript = 1 --Specifies whether sensitive values should be decrypted in script

When the procedure copies the configuration, it applies the source configurations to target. If on the target are any other configuration, those are not touched by the cloning script. In order to have exact configuration, the configurations on target project should be reset.

The generated script is also easily reusable as there are two variables in the beginning for specification of the destination folder and project. By simple modification of those variables the script will apply the configuration on different projects in different folders.

Parameters

The procedure has several parameters where some are required and some optional

The core parameters are @sourceFolder, @sourceProject . The @sourceObject@destinationFolder, @destinationProject, @printScript and @decryptSensistiveInScript are optional.

@source Folder

Specifies the source folder where the source project is located

@sourceProject

Specifies the name of the source project from within source folder to be scripted

@sourceObject

Optional name of the Source Object. It specifies name of the object withing the project which configuration should be scripted. It can point to project name or to individual package names. When provided, then only configurations related to that object are cloned or scripted.

@destinationFolder

Name of the folder of destination project to which the configuration should be cloned. This parameter is optional and if not provided than the source folder name is being used.

@destinationProject

Name of the destination project to which the configuration should be coned. This parameter is optional and if not provided, then the name of the source project is used. If the destination is not provided or matches the source project (both folders and projects are the same), then the @printScript parameter is enforced to 1 and script is always generated.

@printScript

Specifies whether a script should be printed into the output window. If yes, then you simply copy the script from the output window and you can apply it immediatelly on other SSIS Instance.

Default value = 0, this means that the script executes within the SSISDB catalog and automatically clones the configuration unless it is enforced when source matches destination or destination is not provided.

@decryptSensitiveInScript

Specifeis whether sensitive values should be decrypted in the script.If yes, then the values are part of the script in plain-text form, however they are marked sensitive and will be encrypted after applying the script.

If the parameter is 0 (Default), then the sensitive values are not scripted out and instead NULL value is being scripted out. User must then fill those missing values prior execution of the script.

Sample Output

Below is sample script generated by the stored procedure when @printScript=1 and @decryptSensitiveInScript=1

RAISERROR(N'sp_SSISCloneConfiguration v0.10 (2016-12-18) (C) 2016 Pavel Pawlowski', 0, 0) WITH NOWAIT;
RAISERROR(N'=====================================================================', 0, 0) WITH NOWAIT;

DECLARE @destinationFolder nvarchar(128) = N'Test' --Specify Destination Folder Name
DECLARE @destinationProject nvarchar(128) = N'DataLoading' --Specify Destination Project Name

--Checking for destination folder existence
IF NOT EXISTS(SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE [name] = @destinationFolder)
BEGIN
RAISERROR(N'Destination folder [%s] does not exists.', 15, 0, @destinationFolder) WITH NOWAIT;
RETURN;
END
--Checking for destination project existence
IF NOT EXISTS(SELECT 1
FROM [SSISDB].[catalog].[projects] p
INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = p.folder_id
WHERE f.name = @destinationFolder AND p.name = @destinationProject)
BEGIN
RAISERROR(N'Destination project [%s]\[%s] does not exists.', 15, 1, @destinationFolder, @destinationProject) WITH NOWAIT;
RETURN;
END

--Project parameters configuration
DECLARE @var sql_variant

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoading]\[SourceDB_ConnectionString]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name = N'SourceDB_ConnectionString', @object_name = @destinationProject, @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'R', @parameter_value = N'SourceDB_ConnectionString'

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoading]\[Interval]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
SET @var = CONVERT(int, N'120');
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name = N'Interval', @object_name = @destinationProject, @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'V', @parameter_value = @var

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoading]\[SecurePassword]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'Pa$$w0rd'); --SENSITIVE
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name = N'SecurePassword', @object_name = @destinationProject, @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'V', @parameter_value = @var

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoad.dtsx]\[SourceTableName]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'Person');
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=30, @parameter_name = N'SourceTableName', @object_name = N'DataLoad.dtsx', @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'V', @parameter_value = @var
RAISERROR(N'-----------------------------------------------------------------------------', 0, 0) WITH NOWAIT;
RAISERROR(N'There are configurations using Environment varaibles references.', 0, 0) WITH NOWAIT;
RAISERROR(N'DON''T FORGET TO SET ENVIRONMENT REFERENCES for project [%s]\[%s].', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT
RAISERROR(N'-----------------------------------------------------------------------------', 0, 0) WITH NOWAIT;
Advertisements

SQL Server – Cloning User Rights – updated sp_CloneRights on GitHub

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.

Exporting CLR Assemblies from SQL Server back to .dll files

On mssqltips.com you can find my post Exporting CLR Assemblies from SQL Server back to .dll files.

This post describes how you can export the assemblies from SQL server back to physical .dll files. This can be very useful in cases when you do not have the source code for he assemblies inside the DB and you would like to do a security review of the assembly. This commonly happens if you receive the assembly as BYTE code in T-SQL.

SQL Server is wasting space when altering fixed length column

Recently I was going through the archives of posts by Kalen Delaney blog and I came across very interesting post Did you know? — Altering the length of a fixed-length column.  This article gives information how SQL Server is wasting space when you alter a fixed length column and increase its length.

It is interesting so I wanted to take a closer look on this and take a look on the physical db pages to see what happens and to see how the data are stored after alter is done.

Test data preparation and initial view of the data

So first let’s prepare a testing table with some testing data.

CREATE TABLE dbo.AlterTest (
    ID int NOT NULL IDENTITY(1,1),
    col1 char(2000),
    col2 char(1000),
    col3 int
)
GO
INSERT INTO dbo.AlterTest (col1, col2, col3)
VALUES('aaa', 'bbb', ABS(BINARY_CHECKSUM(NEWID())))
GO 4

Now if we take a look on the columns information in metadata tables using Kalen’s query to sys.system_internal_partition_columns we can see the offsets of the table columns.

SELECT  
    c.name AS column_name, 
    column_id, 
    max_inrow_length,
    pc.system_type_id, 
    leaf_offset 
FROM sys.system_internals_partition_columns pc
INNER JOIN sys.partitions p ON p.partition_id = pc.partition_id 
INNER JOIN sys.columns c ON column_id = partition_column_id AND c.object_id = p.object_id
WHERE p.object_id=object_id('AlterTest');
column_name   column_id   max_inrow_length system_type_id leaf_offset
------------- ----------- ---------------- -------------- -----------
ID            1           4                56             4
col1          2           2000             175            8
col2          3           1000             175            2008
col3          4           4                56             3008

As we can see, the physical order is the order in which the columns were defined using the CREATE TABLE statement.

Once we have the data in the table, let’s take a look on how the data are stored. The below query will work only on SQL Server 2008+ and is using undocumented virtual column %%physloc%% which provides information about rows physical location in database and undocumented function sys.fn_PhysLocCracker, which cracks the physical location to human readable FileID, PageID and SlotID.

SELECT
    DB_ID() AS DBID
    ,pl.*
    ,t.*
FROM dbo.AlterTest t
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) pl
DBID   file_id     page_id     slot_id     ID          col1    col2    col3
------ ----------- ----------- ----------- ----------- ------- ------ -----------
16     1           168         0           1           aaa     bbb    587800818
16     1           168         1           2           aaa     bbb    1396332306
16     1           171         0           3           aaa     bbb    844570652
16     1           171         1           4           aaa     bbb    1332601405

Let’s take a look on the first physical page 168.

dbcc traceon (3604,-1)
GO
dbcc page(16,1,168,3)
GO

The partial results are below

PAGE: (1:168)

BUFFER:

BUF @0x00000005011003C0

bpage = 0x000000048C308000          bhash = 0x0000000000000000          bpageno = (1:168)
bdbid = 16                          breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 44191                       bstat = 0xb
blog = 0xab21cccc                   bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x000000048C308000

m_pageId = (1:168)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 98    m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594044350464                                
Metadata: PartitionId = 72057594039959552                                Metadata: IndexId = 0
Metadata: ObjectId = 549576996      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 3012                      m_slotCnt = 2                       m_freeCnt = 2062
m_freeData = 6126                   m_reservedCnt = 0                   m_lsn = (37:227:3)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x62 MIXED_EXT ALLOCATED  80_PCT_FULL                        DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 3015

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 3015

Memory Dump @0x000000002054A060

0000000000000000:   1000c40b 01000000 61616120 20202020 20202020  ..Ä.....aaa         
0000000000000014:   20202020 20202020 20202020 20202020 20202020                      
0000000000000028:   20202020 20202020 20202020 20202020 20202020                      
...
...
00000000000007BC:   20202020 20202020 20202020 20202020 20202020                      
00000000000007D0:   20202020 20202020 62626220 20202020 20202020          bbb         
00000000000007E4:   20202020 20202020 20202020 20202020 20202020                      
...
...
0000000000000BA4:   20202020 20202020 20202020 20202020 20202020                      
0000000000000BB8:   20202020 20202020 f2200923 040000                     ò     #...

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

ID = 1                              

Slot 0 Column 2 Offset 0x8 Length 2000 Length (physical) 2000

col1 = aaa                                                                                                       

Slot 0 Column 3 Offset 0x7d8 Length 1000 Length (physical) 1000

col2 = bbb                                                                                                       

Slot 0 Column 4 Offset 0xbc0 Length 4 Length (physical) 4

col3 = 587800818                    

Slot 1 Offset 0xc27 Length 3015

We can see, that the physical storage corresponds to the metadata stored in the system table sys.system_internal_partition_columns.

Altering the column length and analyzing impacts

Now let’s alter the table and increase the length of Col1 to 3000 characters and take a look what happens.

ALTER TABLE dbo.AlterTest
    ALTER COLUMN Col1 char(3000)
GO

If we take a look on the sys.system_iternal_partition_columns we will see following:

column_name   column_id   max_inrow_length system_type_id leaf_offset
------------- ----------- ---------------- -------------- -----------
ID            1           4                56             4
col2          3           1000             175            2008
col3          4           4                56             3008
col1          2           3000             175            3012

We can see, that the offset of the Col1 has changed and the Column was moved to the end of the record. From here we can see, that the original 2000 bytes were wasted. If we take a look on the physical page 168 as above, we will see, that there is no change in the physical page as the this change to the column is metadata change only.

Impact on new records

So let’s take a look what impact this have on new records added to the table.

INSERT INTO dbo.AlterTest (col1, col2, col3)
VALUES('ccc', 'ddd', ABS(BINARY_CHECKSUM(NEWID())))
GO 4
SELECT
    DB_ID() AS DBID
    ,pl.*
    ,t.*
FROM dbo.AlterTest t
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) pl
DBID   file_id     page_id     slot_id     ID          col1   col2  col3
------ ----------- ----------- ----------- ----------- ------ ----- -----------
16     1           168         0           1           aaa    bbb   587800818
16     1           168         1           2           aaa    bbb   1396332306
16     1           171         0           3           aaa    bbb   844570652
16     1           171         1           4           aaa    bbb   1332601405
16     1           175         0           5           ccc    ddd   2059368981
16     1           177         0           6           ccc    ddd   1449062892
16     1           178         0           7           ccc    ddd   267569086
16     1           179         0           8           ccc    ddd   1325350591

From the results we can see, that the first 4 records originally inserted are occupied only two pages as two records were stored per database page. After the update we can see that each single record is occupied its own page. This is due to the fact that the record length has increased not only by the 1000 characters by which the length of the Col1 was modified but also the original 2000 bytes were wasted. there fore the data length on the page increased from 3008 bytes to 6008 bytes.

Now let’s take a closer look on the physical page. For example the first page occupied by the newly inserted data  (page 175).

dbcc page(16,1,175,3)
GO

Partial results of the DBCC command are here:

PAGE: (1:175)

BUFFER:

BUF @0x0000000503366540

bpage = 0x00000004D7F84000          bhash = 0x000000067753ED81          bpageno = (1:175)
bdbid = 16                          breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 45327                       bstat = 0x10b
blog = 0x212121cc                   bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x00000004D7F84000

m_pageId = (1:175)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 98    m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594044350464                                
Metadata: PartitionId = 72057594039959552                                Metadata: IndexId = 0
Metadata: ObjectId = 549576996      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 6012                      m_slotCnt = 1                       m_freeCnt = 2079
m_freeData = 6111                   m_reservedCnt = 0                   m_lsn = (37:390:11)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          
PFS (1:1) = 0x62 MIXED_EXT ALLOCATED  80_PCT_FULL                        DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 6015

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 6015

Memory Dump @0x000000002267A060

0000000000000000:   10007c17 05000000 df5af8d7 04000000 ffffffff  ..|.....ßZø×....ÿÿÿÿ
0000000000000014:   ffffff7f 9860f8d7 04000000 0040f8d7 04000000  ÿÿÿ..`ø×.....@ø×....
0000000000000028:   01000000 00000000 63000000 00000000 dd7618f4  ........c.......Ýv.ô
0000000000000794:   00000000 00000000 00000000 00000000 01000000  ....................
00000000000007A8:   000071d8 fe070000 c072210e 00000000 00000000  ..qØþ...Àr!.........
00000000000007BC:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000007D0:   00000000 00000000 64646420 20202020 20202020  ........ddd         
00000000000007E4:   20202020 20202020 20202020 20202020 20202020                      
0000000000000BA4:   20202020 20202020 20202020 20202020 20202020                      
0000000000000BB8:   20202020 20202020 157abf7a 63636320 20202020          .z¿zccc     
0000000000000BCC:   20202020 20202020 20202020 20202020 20202020                      
0000000000000BE0:   20202020 20202020 20202020 20202020 20202020                      
000000000000175C:   20202020 20202020 20202020 20202020 20202020                      
0000000000001770:   20202020 20202020 20202020 050000                         ...

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

ID = 5                              

Slot 0 Column 67108865 Offset 0x8 Length 0 Length (physical) 2000

DROPPED = NULL                      

Slot 0 Column 3 Offset 0x7d8 Length 1000 Length (physical) 1000

col2 = ddd                                                                                                       

Slot 0 Column 4 Offset 0xbc0 Length 4 Length (physical) 4

col3 = 2059368981                   

Slot 0 Column 2 Offset 0xbc4 Length 3000 Length (physical) 3000

col1 = ccc

From the output above we can see, the now on the physical page, there is a Column 67108865 (DROPPED) following the Column1 and Column2 has physically moved to the end of the record at offset 0xbc4 (3012).  Also from the page dump we can see that the space from the offset 0x8 to 0x7d7 is containing a mess and that those 2000 bytes are wasted.

As we can see, the original query to sys.system_internals_partition_columns doesn’t show the Column with ID 67108865 (DROPPED). It’s because it uses join to the sys.columns and the DROPPED column is not part of the table, but is par tof the partition. If we use the query without join to the sys.columns it will be shown also in the query output.

SELECT 
    partition_column_id, 
    max_inrow_length,
    pc.system_type_id, 
    leaf_offset,
    is_dropped 
FROM sys.system_internals_partition_columns pc
INNER JOIN sys.partitions p ON p.partition_id = pc.partition_id 
WHERE p.object_id=object_id('AlterTest');
partition_column_id max_inrow_length system_type_id leaf_offset is_dropped
------------------- ---------------- -------------- ----------- ---------
1                   4                56             4           0
67108865            2000             175            8           1
3                   1000             175            2008        0
4                   4                56             3008        0
2                   3000             175            3012        0

It Seems that the dropped columns have IDs starting from 67108865 and the numbers increase as there are more dropped columns. Also the DROPPED columns have flag is_dropped = 1.

Multiple updates of the column size

In previous examples we took a look on the update to a single column and in Delaney’s post you can see the result if we want to update multiple column. In case we try to multiple columns, space allocated for all the original columns is dropped and new space allocated. But what happens if we update the same column multiple times? Let’s make a simple test.

CREATE TABLE AlterTest2 (
    id int not null identity(1,1),
    Col1 char(1000),
    Col2 char(1000),
    Col3 int
)
GO
ALTER TABLE AlterTest2
ALTER COLUMN Col1 char(2000)
GO
ALTER TABLE AlterTest2
ALTER COLUMN Col1 char(2500)
GO
SELECT 
    partition_column_id, 
    max_inrow_length,
    pc.system_type_id, 
    leaf_offset,
    is_dropped 
FROM sys.system_internals_partition_columns pc
INNER JOIN sys.partitions p ON p.partition_id = pc.partition_id 
WHERE p.object_id=object_id('AlterTest2');
partition_column_id max_inrow_length system_type_id leaf_offset is_dropped
------------------- ---------------- -------------- ----------- ----------
1                   4                56             4           0
67108865            1000             175            8           1
3                   1000             175            1008        0
4                   4                56             2008        0
67108866            2000             175            2012        1
2                   2500             175            4012        0

From the example we can see, that each change which increase the fixed column length causes that the original column space is dropped and additional space is allocated.

Recovering the wasted space

It is great, that extending the column width of fixed length column is a metadata only operation as it is very quick and avoids blocking especially on large tables, but on the other side as we can see, this can cause a significant space wasting.

In case there will be less new inserts into the table than the current about of rows we do not need to take care about the wasted space much (from the point of wasted storage space) as the metadata change didn’t affect the current records and on the current records we are saving the space as we have extended the record length and only the new records inserted are wasting the space.

On the other side, if we know there will be a lot of inserts and reads of the newly inserted records, than it’s a good idea to recover the wasted space as the data will consume more space and further reads will have to read more unnecessary data.

In the comments to the original post there are some suggestions how to resolve the problem.

From my perspective if the table is already clustered, we do not need to crop and recreate the clustered index, but it is enough to REBUILD the index. The rebuild operation will reorder the data and free up the wasted space and also write the original data with new record length.

In the case of heap, creating and dropping clustered index will be quite costly operation which will move the data twice. On small tables this doesn’t matter but on larger amounts of data it will be better to do simple SELECT * INTO newTable from aTable and than simply drop the original table and rename the new one to the original one.

SELECT
*
INTO dbo.AlterTest2
FROM dbo.AlterTest
GO
SELECT
    DB_ID() AS DBID
    ,pl.*
    ,t.*
FROM dbo.AlterTest2 t
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) pl
GO
DBID   file_id     page_id     slot_id     ID          col1  col2  col3
------ ----------- ----------- ----------- ----------- ----- ----- -----------
16     1           411         0           1           aaa   bbb   587800818
16     1           411         1           2           aaa   bbb   1396332306
16     1           412         0           3           aaa   bbb   844570652
16     1           412         1           4           aaa   bbb   1332601405
16     1           413         0           5           ccc   ddd   2059368981
16     1           413         1           6           ccc   ddd   1449062892
16     1           414         0           7           ccc   ddd   267569086
16     1           414         1           8           ccc   ddd   1325350591

We can see, that the new table has no wasted space and all pages are now allocated by two records.

Of course if we there are some foreign keys and indexes than those will have to be recreated. Anyway this will cost much less I/O and processing power than creating and dropping the clustered key

 Conclusion

As we can see fro the examples above, altering the fixed column length and increasing it, even it’s metadata  only operation causes, that the originally allocated space is dropped and new space is allocated in the row for all newly inserted or updated rows in the table. So be carefull when altering a fixed length columns especially when you are doing multiple alters to a single column as significat space can be wasted. Knowing the fact that the dropped columns have flag is_dropped = 1 in the sys.system_internals_partition_columns, we can use a below query to list all the tables containing DROPPED columns with wasted space.

SELECT distinct
    p.partition_id,
    p.object_id,
    o.name as table_name
FROM sys.system_internals_partitions p
INNER JOIN sys.objects o on p.object_id = o.object_id
INNER JOIN sys.system_internals_partition_columns pc ON p.partition_id = pc.partition_id
WHERE pc.is_dropped = 1
partition_id         object_id   table_name
-------------------- ----------- -----------
72057594040025088    1253579504  AlterTest2
72057594040090624    1269579561  AlterTest

MS SQL 2012 Window functions introduction

Among lot of new features introduced in SQL Server 2012 also a new Windowing functions were introduced. The new functionality allow us to use the ORDER BY clause in the OVER clause with aggregate functions and also new ROWS and RANGE clauses were introduced to limit rows. The ORDER BY allow us define the order of rows processing and the ROWS/RANGE clauses put limits on the rows being processed in partition. All the details related to the OVER clause you can find on MSDN: OVER Clause (Transact-SQL).

ROWS/RANGE clause

The ROWS clause limits the rows in a parittion by specifying a fixed number of rows preceding or folowing the current rows. The rows preceeding and following are determined by the order specified in the ORDER BY clause.

The limit can be specified by serveral methods:

  • <unsigned integer> PRECEDING -fixed number of preceding rows
  • CURRENT ROW – representing current row being processed
  • UNBOUNDED PRECEDING – all previous records
  • <unsigned integer> FOLLOWING – fixed number of following rows
  • UNBOUNDED FOLLOWING – all rows following current row

So we can specify the limits like

ROWS BETWEEN 3 PRECEEDING AND 1 FOLLOWING
ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING

RANGE BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
RENGE CURRENT ROW

The RANGE clause can be only used with the UNBOUNDED limit and CURRENT ROW. The difference between ROWS and RANGE clause is, that ROWS works with physical rows and RANGE works with range of rows based on the current row value in the terms of ORDER BY clause. This means that for ROWS clause the CURRENT ROW represents the only current row being processed. For RANGE the CURRENT ROW represents all the rows with the same value in the fields specified in the ORDER BY clause within current partition as the current row being processed. So if we use RANGE and multiple rows have the same rank in the terms of order within the partition, then all those rows will represent current row.

When there is no ROWS/RANGE clause specified after the ORDER BY clause, then the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is used by SQL Server.

Samples how to use the window functions

Let’s take a look on a few samples, how we can use the window functions and what results they will provide.

Test data preparation

To be able to test the new functionality

--======================
-- Create test database
--======================
CREATE DATABASE WindowFunctionsTest
GO
USE WindowFunctionsTest
GO 

--Create Testing Tables
CREATE TABLE [dbo].[Accounts](
	[TransactionID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	[TransactionDate] [datetime] NULL,
	[Balance] [float] NULL
)
GO
CREATE TABLE [dbo].[MultiAccounts](
	[TransactionID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	[AccountID] [int] NOT NULL,
	[TransactionDate] [datetime] NULL,
	[Balance] [float] NULL
)
GO
--Fill test tables with data
INSERT INTO [dbo].[Accounts](
	[TransactionDate],
	[Balance]
)
SELECT '2000-1-1', 100 UNION ALL
SELECT '2000-1-1', -50 UNION ALL
SELECT '2000-1-2', 200 UNION ALL
SELECT '2000-1-3', 500 UNION ALL
SELECT '2000-1-4', -200 UNION ALL
SELECT '2000-1-5', 1000 UNION ALL
SELECT '2000-1-5', -300 UNION ALL
SELECT '2000-1-6', -300 UNION ALL
SELECT '2000-1-7', -200 UNION ALL
SELECT '2000-1-8', 2000 UNION ALL
SELECT '2000-1-9', 100 UNION ALL
SELECT '2000-1-10', -50 UNION ALL
SELECT '2000-1-10', 500 UNION ALL
SELECT '2000-1-11', 200 UNION ALL
SELECT '2000-1-12', 200 UNION ALL
SELECT '2000-1-13', 1000 UNION ALL
SELECT '2000-1-14', 1000 UNION ALL
SELECT '2000-1-15', -500 UNION ALL
SELECT '2000-1-15', -300 UNION ALL
SELECT '2000-1-16', 1000 UNION ALL
SELECT '2000-1-17', 1000 UNION ALL
SELECT '2000-1-18', -800 UNION ALL
SELECT '2000-1-19', 2000 UNION ALL
SELECT '2000-1-20', -1000
GO

INSERT [dbo].[MultiAccounts] (
	[AccountID],
	[TransactionDate],
	[Balance]
)
SELECT 1, '2000-1-1', 100 UNION ALL
SELECT 1, '2000-1-1', -50 UNION ALL
SELECT 1, '2000-1-2', 200 UNION ALL
SELECT 1, '2000-1-3', 500 UNION ALL
SELECT 1, '2000-1-4', -200 UNION ALL
SELECT 1, '2000-1-5', 1000 UNION ALL
SELECT 1, '2000-1-5', -300 UNION ALL
SELECT 1, '2000-1-6', -300 UNION ALL
SELECT 1, '2000-1-7', -200 UNION ALL
SELECT 2, '2000-1-1', 2000 UNION ALL
SELECT 2, '2000-1-2', 100 UNION ALL
SELECT 2, '2000-1-3', -50 UNION ALL
SELECT 2, '2000-1-4', 500 UNION ALL
SELECT 2, '2000-1-5', 200 UNION ALL
SELECT 2, '2000-1-6', 200 UNION ALL
SELECT 2, '2000-1-7', 1000 UNION ALL
SELECT 2, '2000-1-7', 1000 UNION ALL
SELECT 3, '2000-1-1', 800 UNION ALL
SELECT 3, '2000-1-2', -300 UNION ALL
SELECT 3, '2000-1-3', 1000 UNION ALL
SELECT 3, '2000-1-4', 1000 UNION ALL
SELECT 3, '2000-1-5', -800 UNION ALL
SELECT 3, '2000-1-6', 2000 UNION ALL
SELECT 3, '2000-1-7', -1000
GO

Window functions samples

If we try any of below queries they will provide the same results

--Using the ROWS clause
SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate, TransactionID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID
GO
--The same as abowe the ROWS UNBOUNDED PRECEDING will be completed by SQL Server to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
--If we specify only the left boundary, SQL Servers automaticaly fills the right BOUNDARY
SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate, TransactionID ROWS UNBOUNDED PRECEDING ) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID
GO
--Using the RANGE Clause
SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate, TransactionID RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID
GO
--The same as above as RANGE UNBOUNDED PRECEDING AND CURRENT ROW will be complete by SQL Server as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
--If we specify only the left boundary, SQL Servers automaticaly fills the right BOUNDARY
SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate, TransactionID RANGE UNBOUNDED PRECEDING ) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID
GO
--NO ROWS/RANGE Clause (SQL Server will use the DEFAULT RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate, TransactionID) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID
GO

Results are below and we can see, a correct cumulative balance is calculated.

TransactionID TransactionDate         Balance                CummulativeBalance
------------- ----------------------- ---------------------- ----------------------
1             2000-01-01 00:00:00.000 100                    100
2             2000-01-01 00:00:00.000 -50                    50
3             2000-01-02 00:00:00.000 200                    250
4             2000-01-03 00:00:00.000 500                    750
5             2000-01-04 00:00:00.000 -200                   550
6             2000-01-05 00:00:00.000 1000                   1550
.             .                       .                      .
.             .                       .                      .
.             .                       .                      .
20            2000-01-16 00:00:00.000 1000                   5900
21            2000-01-17 00:00:00.000 1000                   6900
22            2000-01-18 00:00:00.000 -800                   6100
23            2000-01-19 00:00:00.000 2000                   8100
24            2000-01-20 00:00:00.000 -1000                  7100

ROWS clause with not unique order

SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate
GO

Results will be the same as in previous example. They are the same because there is no parallelism and we have CLUSTERED INDEX on the TransactionID (otherwise the final order could be different because the order of rows with the same TransactionDate is not guaranteed here.

TransactionID TransactionDate         Balance                CummulativeBalance
------------- ----------------------- ---------------------- ----------------------
1             2000-01-01 00:00:00.000 100                    100
2             2000-01-01 00:00:00.000 -50                    50
3             2000-01-02 00:00:00.000 200                    250
4             2000-01-03 00:00:00.000 500                    750
5             2000-01-04 00:00:00.000 -200                   550
6             2000-01-05 00:00:00.000 1000                   1550
7             2000-01-05 00:00:00.000 -300                   1250
.             .                       .                      .
.             .                       .                      .
.             .                       .                      .
20            2000-01-16 00:00:00.000 1000                   5900
21            2000-01-17 00:00:00.000 1000                   6900
22            2000-01-18 00:00:00.000 -800                   6100
23            2000-01-19 00:00:00.000 2000                   8100
24            2000-01-20 00:00:00.000 -1000                  7100

RANGE Clause with not unique order

SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID
GO

Here we see, that the results are quite different. The final sum is the same, but the intermediate are not.

TransactionID TransactionDate         Balance                CummulativeBalance
------------- ----------------------- ---------------------- ----------------------
1             2000-01-01 00:00:00.000 100                    50
2             2000-01-01 00:00:00.000 -50                    50
3             2000-01-02 00:00:00.000 200                    250
4             2000-01-03 00:00:00.000 500                    750
5             2000-01-04 00:00:00.000 -200                   550
6             2000-01-05 00:00:00.000 1000                   1250
7             2000-01-05 00:00:00.000 -300                   1250
8             2000-01-06 00:00:00.000 -300                   950
.             .                       .                      .
.             .                       .                      .
.             .                       .                      .
17            2000-01-14 00:00:00.000 1000                   5700
18            2000-01-15 00:00:00.000 -500                   4900
19            2000-01-15 00:00:00.000 -300                   4900
20            2000-01-16 00:00:00.000 1000                   5900
21            2000-01-17 00:00:00.000 1000                   6900
22            2000-01-18 00:00:00.000 -800                   6100
23            2000-01-19 00:00:00.000 2000                   8100
24            2000-01-20 00:00:00.000 -1000                  7100

Here we can see, that the RANGE works as described above. All rows with the same value in the ORDER BY clause are considered as current row. Therefore for the dates ‘2000/01/01’ ,  ‘2000/01/05’ and ‘2000/01/15’ the values for each date are the same.

Working with FOLLOWING Rows

All the examples above worked with current row and all previous rows. Except this we can even work with rows following current row in particular order.

Here are a few other examples incorporating also FOLLOWING rows.

--Sum of current row and all following rows
SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate, TransactionID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID

--SUM of 1 preceding, current and one following row
SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (ORDER BY TransactionDate, TransactionID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS CummulativeBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID

--SUM of all rows in each row
SELECT
    [TransactionID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER () AS FinalBalance
FROM [dbo].[Accounts]
ORDER BY TransactionDate, TransactionID

Example with Partitioning results

In previous examples we have worked with single partition. The OVER clause also allows partitioning the results. So let see some a few examples with partitioning.

--Sum of current row and all following rows partitioned, by AccountID
SELECT
    [TransactionID]
	,[AccountID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (PARTITION BY [AccountID] ORDER BY TransactionDate, TransactionID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS CummulativeBalance
FROM [dbo].[MultiAccounts]
ORDER BY AccountID, TransactionDate, TransactionID

--SUM of 1 preceding, current and one following row, partitioned by AccountID
SELECT
    [TransactionID]
	,[AccountID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (PARTITION BY [AccountID] ORDER BY TransactionDate, TransactionID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS CummulativeBalance
FROM [dbo].[MultiAccounts]
ORDER BY AccountID, TransactionDate, TransactionID

--SUM of all rows in each row
SELECT
    [TransactionID]
	,[AccountID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (PARTITION BY [AccountID]) AS FinalBalance
FROM [dbo].[MultiAccounts]
ORDER BY AccountID, TransactionDate, TransactionID

--SUM of all preceeding and current row, partitioned by AccountID order is based only on TransactionDate - using RANGE
SELECT
    [TransactionID]
	,[AccountID]
    ,[TransactionDate]
    ,[Balance]
    ,SUM(Balance) OVER (PARTITION BY [AccountID] ORDER BY TransactionDate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CummulativeBalance
FROM [dbo].[MultiAccounts]
ORDER BY AccountID, TransactionDate

When to use ROWS and when RANGE

Now we can ask, when we should use the ROWS clause and when RANGE clause to limit the rows. The answer comes fro the definition how the ROWS and RANGE clauses works. As described, ROWS works with each unique rows and RANGE handles multiple rows with the same order position as current row.

So in case the combination of fields specified in the ORDER BY clause does not uniquely specify the order of rows (as in case of examples above when only TransactionDate was used), then you should use RANGE, as the processing order of rows with the same order position is not guaranteed. In case the rows are uniquely identified, then ROWS should be used as there are no rows with the same order in the partition.

Conclusion

The new windowing functions brings new possibilities in writing T-SQL queries can simplify a lot of tasks which were problematic to write without these constructs. It allow us to bypass the recursive CTE, other solutions for calculation of running totals or averages without knocking down the server and also allow us to bypass quirky updates, CLR solutions which have some pitfalls when are used.

In my next post I will take a closer look on the Running Totals problem when using this new windowing functionality. Also I will take a closer look on the query plans produced by those constructs and give some advices for using them.

Removing Accent (diacritics) using CLR

Some times it may happen, that you need to remove accent (diacritics) from string when you are querying data or when you are storing data into particular table.

There are several methods how to achieve this. One of the possible solution is using a CONVERT with COLLATE as Twitted by MVP Mladen Prajdic and mentioned on Luke Jian blog, but this method has an issue I will mention below.

Other solution could be replacement of accented characters with the ones without accent. But this is a very problematic solution as you have to do a lot of replacement (the performance can suffer) and it will be problematic to cover all the possible accent characters and you easily miss one. Similar to this could be creation of a replacement table and implementation of function similar to Splitting function using Tally Table (Jeff Moden). But again you will have to write all possible combination of accented and not accented characters into the replacement table.

So if you do not want to change the collation and would like to avoid possible issues of the CONVERT method and avoid writing replacement tables etc., you can easily use a very simple amd known CLR method. This method is using string normalization and CharUnicodeInfo class from the System.Globalization name space. Based on this you can write a very simple scalar CLR function.

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Globalization;

public class StringsCommon
{
    /// <summary>
    /// Removes Accent (Diacritics) from string
    /// </summary>
    /// <param name="sourceString">Source string fro wchich accent should be removed</param>
    /// <returns>string without accent</returns>
    [SqlFunction(IsDeterministic = true)]
    public static SqlChars RemoveAccent(SqlString sourceString)
    {
        if (sourceString.IsNull)
            return SqlChars.Null;

        string normalized = sourceString.Value.Normalize(NormalizationForm.FormD);

        StringBuilder output = new StringBuilder(sourceString.Value.Length);

        foreach (char ch in normalized)
        {
            if (CharUnicodeInfo.GetUnicodeCategory(ch) != UnicodeCategory.NonSpacingMark)
                output.Append(ch);
        }
        return new SqlChars(output.ToString());
    }
}

Once you compile above mentioned function into an .net assembly, you can register the assembly and function in your database.

CREATE ASSEMBLY [PPSqlClrSafe]
AUTHORIZATION [dbo]
FROM 'C:CLRPPSqlClrSafe.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE FUNCTION [ClrSafe].[fn_RemoveAccent](
	@sourceString [nvarchar](max)  --Source string to remove accent
)
RETURNS [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PPSqlClrSafe].[StringsCommon].[RemoveAccent]
GO</pre>
</div>
Once the function is registered, you can easily use it to remove accent (diacritics) from whatever string you want
<div style="max-height:400px;overflow:auto;">
<pre class="brush:sql">WITH Data AS (
    SELECT
        'Czech' AS [Language]
        ,N'á, é, í, ó, ú, ý, č, ď, ě, ň, ř, š, ť, ž, ů' AS [AccentChars] UNION ALL
    SELECT
        'Norwegian' AS [Language]
        ,N'ø' AS [AccentChars] UNION ALL
    SELECT
        'Estonian' AS [Language]
        ,N'õ' AS [AccentChars] UNION ALL
    SELECT
        'French' AS [Language]
        ,N'à, â, ç, é, è, ê, ë, î, ï, ô, ù, û, ü' AS [AccentChars] UNION ALL
    SELECT
        'Croatian, Vietnamese' AS [Language]
        ,N'đ' AS [AccentChars] UNION ALL
    SELECT
        'Latvian' AS [Language]
        ,N'ā, ē, ī, ū, ļ, ķ, ņ, ģ' AS [AccentChars] UNION ALL
    SELECT
        'Hungarian' AS [Language]
        ,N'ö, ü, ő, ű' AS [AccentChars] UNION ALL
    SELECT
        'Polish' AS [Language]
        ,N'ą, ć, ę, ń, ó, ś, ź, ż' AS [AccentChars] UNION ALL
    SELECT
        'Romanian' AS [Language]
        ,N'ă, â, î, ș, ț' AS [AccentChars] UNION ALL
    SELECT
        'Slovak' AS [Language]
        ,N'ô, ť, ľ, ŕ, ĺ, ä' AS [AccentChars] UNION ALL
    SELECT
        'Spanish' AS [Language]
        ,N'ñ' AS [AccentChars] UNION ALL
    SELECT
        'Swedish' AS [Language]
        ,N'å' AS [AccentChars] UNION ALL
    SELECT
        'Turkish' AS [Language]
        ,N'ç, ş, ğ' AS [AccentChars] UNION ALL
    SELECT
        'Greece' AS [Language]
        ,N'Γ, δ, ξ, Φ' AS [AccentChars]
)
SELECT
    [Language]
    ,[AccentChars]
    ,[ClrSafe].fn_RemoveAccent([AccentChars]) AS [RemovedAccent]
FROM Data

Which produces below result:

Language             AccentChars                                 RemovedAccent
-------------------- ------------------------------------------- --------------------------------------------
Czech                á, é, í, ó, ú, ý, č, ď, ě, ň, ř, š, ť, ž, ů a, e, i, o, u, y, c, d, e, n, r, s, t, z, u
Norwegian            ø                                           ø
Estonian             õ                                           o
French               à, â, ç, é, è, ê, ë, î, ï, ô, ù, û, ü       a, a, c, e, e, e, e, i, i, o, u, u, u
Croatian, Vietnamese đ                                           đ
Latvian              ā, ē, ī, ū, ļ, ķ, ņ, ģ                      a, e, i, u, l, k, n, g
Hungarian            ö, ü, ő, ű                                  o, u, o, u
Polish               ą, ć, ę, ń, ó, ś, ź, ż                      a, c, e, n, o, s, z, z
Romanian             ă, â, î, ș, ț                               a, a, i, s, t
Slovak               ô, ť, ľ, ŕ, ĺ, ä                            o, t, l, r, l, a
Spanish              ñ                                           n
Swedish              å                                           a
Turkish              ç, ş, ğ                                     c, s, g
Greece               Γ, δ, ξ, Φ                                  Γ, δ, ξ, Φ

When you compare it to the CONVERT with COLLATION method, you can find that this doesn’t have the problem with non existen characters. As the CONVERT converts the characters to particular character set which does not need to contain all the charecters of the source character set. We can see this e.g. on the Norwegian, and Greece characters.

If we use the CONVERT with COLLATION method we receive:

WITH Data AS (
    SELECT
        'Czech' AS [Language]
        ,N'á, é, í, ó, ú, ý, č, ď, ě, ň, ř, š, ť, ž, ů' AS [AccentChars] UNION ALL
    SELECT
        'Norwegian' AS [Language]
        ,N'ø' AS [AccentChars] UNION ALL
    SELECT
        'Estonian' AS [Language]
        ,N'õ' AS [AccentChars] UNION ALL
    SELECT
        'French' AS [Language]
        ,N'à, â, ç, é, è, ê, ë, î, ï, ô, ù, û, ü' AS [AccentChars] UNION ALL
    SELECT
        'Croatian, Vietnamese' AS [Language]
        ,N'đ' AS [AccentChars] UNION ALL
    SELECT
        'Latvian' AS [Language]
        ,N'ā, ē, ī, ū, ļ, ķ, ņ, ģ' AS [AccentChars] UNION ALL
    SELECT
        'Hungarian' AS [Language]
        ,N'ö, ü, ő, ű' AS [AccentChars] UNION ALL
    SELECT
        'Polish' AS [Language]
        ,N'ą, ć, ę, ń, ó, ś, ź, ż' AS [AccentChars] UNION ALL
    SELECT
        'Romanian' AS [Language]
        ,N'ă, â, î, ș, ț' AS [AccentChars] UNION ALL
    SELECT
        'Slovak' AS [Language]
        ,N'ô, ť, ľ, ŕ, ĺ, ä' AS [AccentChars] UNION ALL
    SELECT
        'Spanish' AS [Language]
        ,N'ñ' AS [AccentChars] UNION ALL
    SELECT
        'Swedish' AS [Language]
        ,N'å' AS [AccentChars] UNION ALL
    SELECT
        'Turkish' AS [Language]
        ,N'ç, ş, ğ' AS [AccentChars] UNION ALL
    SELECT
        'Greece' AS [Language]
        ,N'Γ, δ, ξ, Φ' AS [AccentChars]
)
SELECT
    [Language]
    ,[AccentChars]
    ,CONVERT(varchar(50), [AccentChars]) COLLATE Cyrillic_General_CI_AI AS [RemovedAccent]
FROM Data

Results:

Language             AccentChars                                 RemovedAccent
-------------------- ------------------------------------------- --------------------------------------------
Czech                á, é, í, ó, ú, ý, č, ď, ě, ň, ř, š, ť, ž, ů a, e, i, o, u, y, c, d, e, n, r, s, t, z, u
Norwegian            ø                                           o
Estonian             õ                                           o
French               à, â, ç, é, è, ê, ë, î, ï, ô, ù, û, ü       a, a, c, e, e, e, e, i, i, o, u, u, u
Croatian, Vietnamese đ                                           d
Latvian              ā, ē, ī, ū, ļ, ķ, ņ, ģ                      a, e, i, u, l, k, n, g
Hungarian            ö, ü, ő, ű                                  o, u, o, u
Polish               ą, ć, ę, ń, ó, ś, ź, ż                      a, c, e, n, o, s, z, z
Romanian             ă, â, î, ș, ț                               a, a, i, ?, ?
Slovak               ô, ť, ľ, ŕ, ĺ, ä                            o, t, l, r, l, a
Spanish              ñ                                           n
Swedish              å                                           a
Turkish              ç, ş, ğ                                     c, s, g
Greece               Γ, δ, ξ, Φ                                  ?, ?, ?, ?

From above we can see, that the CLR solution provides much better results and you do not loose any characters by conversion to non Unicode character set

If you are interested, you can grab sample C# project here: PPSqlClrSafe_AccentRemoval,zip

Any comments are welcomed.

SSIS 2008 Columns To XML Data Flow Transformation

This article represents a custom components for transformation of columns inside SSIS Data Flow into XML.

If you are writing ETL processes using SSIS, you many times comes to situation, when you need log errors or other problems which can occur during ETL process. An example can be a failure of data conversion during the ETL process. In that situation you mostly would like to know that record exactly caused that error and log such conversion errors into particular error table.

Problem occurs when you have a lot of tables and want to see what data were converted and caused that particular error. As SSIS requires that the sources and destinations needs to be strongly mapped and you have different sources with different structures, this leads to a situation where you have a lot of different error tables – generally one per the source structure at which an error can occur.

This situation can be easily solved by a custom Data Flow component which will convert all source columns into an XML. In that situation you can only have one Error Table, which will contain e.g. Error Code, Error Column, Description and an XML Field in which will be all the data of particular row which caused error encoded in XML Form. This rapidly simplifies error logging and also error log monitoring as you need to monitor only a single table.

So the SSIS Data Flow with the Custom Columns to XML Transformation could look like the one below.

Columns To Xml Data FLow

ColumnsToXML PropertiesColumnsToXML Input Columns

You can provide a SourceID and SourceName properties which are then part of the produced XML so you can easily identify the source. In the Input Columns you select columns you want to be written to the XML. You can also specify an OutpuAlias and the the column names written into the XML will be the one specified in the OutputAlias property.

The Log written using that component and the XML produced will look like the ones below.

ColumnsToXML Log

<row sourceID="1" sourceName="Test Data Conversion">
  <Column name="Name" id="235" lineageId="72">Joseph</Column>
  <Column name="ValueInt" id="236" lineageId="75">2</Column>
  <Column name="ValueDate" id="237" lineageId="78">2012/01/35</Column>
</row>

Using the .value method of XML data type we can easily querying necessary information from the log table for whatever source in the table. So for our sample scenario we would like to query the Name, ValueInt and ValueDate columns stored as XML. This can be easily achieved by below SQL query:

SELECT
    [RowID]
    ,[ErrorCode]
    ,[ErrorColumn]
    ,[ColumnsXMLData].value('data((/row/Column[@name="Name"])[1])', 'varchar(128)') AS Name
    ,[ColumnsXMLData].value('data((/row/Column[@name="ValueInt"])[1])', 'varchar(10)') AS ValueInt
    ,[ColumnsXMLData].value('data((/row/Column[@name="ValueDate"])[1])', 'varchar(10)') AS ValueDate
FROM [TestDB].[dbo].[ETLErrorLog]
WHERE [ColumnsXMLData].value('data((/row/@sourceID)[1])', 'int') = 1

As you can see from the samples above, you can create easily create single Error table for whatever ETL logging and simplify the logging rapidly as you#160; do not need to create the error tables with separate structures for each source.

ColumnsToXML Implementation

The ColumnsToXmlTransform is a custom implementation of the PipelineComponent. It is developed in C# for .NET framework 3.5 and uses Linq to XML for creating the XML output.

The component processes selected input columns, generates the XML and stores it in the output column called ColumnsXMLData.

Except the ColumsXMLDataSourceID and SourceName) to allow you to identify the source row source once it is written as XML into the database and simplifies querying for data coming from particular sources as showed above.

Once the component is built, it is necessary to install it into the Global Assembly Cache and to the PipelineComponents folder in the SQL Server Integration services installation path on the machine on which the SSIS using this component will be running.

To install it into a GAC you can use the gacutil.exe which is part of the Windows SDK.

Component is now available publicly with other components on GitHub as projects for SSIS 2008 – SSIS 2016.

If you run the development environment as with elevated Administrator privileges, the project contains Post Build events, which will install the assembly into the GAC (it assumes you have Windows SDK 7.0 installed – if you have newer version, modify the path to the gacutil.exe accordingly) and also copies the assembly into the codePipelineComponents/code folder of the default SQL Server Integration services installation. If you installation directory differ, please modify the paths in Post Build events accordingly.

Once you have compiled the code, installed in GAC and copied to the PipelineComponents folder, you can include the component to the data flow items palette. Right-click on the palette and select Choose Items

Choose Data Flow Transformation Items Pop-upChoose SSIS Data Flow Items

Once you select the component, it will appear on the toolbox and you can start using it.

Columns To XML On Palette

Once I will have more time, I will try to create an installer for the component. Anyway, any comments are welcome.