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

Generating Partition Function – sp_tblCreatePartitionFunction available don GitHub

If you are working with large databases you have come to situation when you need to partition the large tables.

To be able to partition a table, you need to create a Partition Function. Creation of Partition Function is quite easy, however if you need to create a larger number of partitions at initial phase, it can be pretty annoying as you have to define all the ranges for each partition. Also it is easy to make a type in the list of boundary values and as a result create a partition function with wrong partition alignment.

To simplify that process I’m presenting here a stored procedure which takes care about that and generates the partition function automatically based on input parameters

sp_tblCreatePartitionFunction

As mentioned above, the sp_tblCreatePartitionFunction takes care about generation of the partition function based on input parameters.

You can find complete source code in my SQL-Scripts project on GitHub under the TablesManagement\Partitioning folder.

The stored procedure provides support for generation of ranges based on smallint, int, bigint, date, datetime and datetime2 data types.

Stored procedure generates a partition function with boundary values between @rangeStart and @rangeEnd parameters.

The stored procedure is marked as system stored procedure in the script so it means that it can operate in the context of the current user database.

Parameters

The input parameters define the ranges and the way how the partition function is generated

The available input parameters for the function are @pfName, @rangeStart, @rangeEnd, @boundaryType, @incrementValue, @incrementUnit, @useIntegerDates, @integerFormatType and @printScriptOnly

@pfName

Defines the partition function Name. the parameter data type is nvarchar(128) which corresponds to sysname.

@rangeStart

Defines the starting range for generation of the partition function. The parameter is sql_variant. You have to pass parameter of supported data type for the range. As mentioned above the supported data types are smallint, int, bigint, date, datetime and datetime2.

To simplify specification of the range, you can pass the parameter as string. If the value passed as string represents a DateTime it is automatically converted to datetime data type.

You can also pass a value with a data type specifier. The data type specifier must be first character of the string and the value must follow. The supported data type specifiers are 'D' - date, 'T' - datetime, 'B' - bigint, 'I' - int and 'S' - smallint.

Below are some examples of supported strings passed as @rangeStart :

Sample value Result
'2016-01-01' Converted to datetime 2016-01-01
20160101 Converted to datetime 2016-01-01
D2016-01-01 Converted to date 2016-01-01
T20160101 Converted to datetime 2016-01-01
B1010 Converted to bigint 100
I1010 Converted to int 100
S1010 Converted to smallint 100

The @rangeStart representing a date can be altered during the partition function generation based on the @incrementUnit parameter. The @rangeStart is shifted to the beginning of corresponding unit.. See the @incrementUnit parameter for details.

The @rangeStart is included in the generated ranges.

@rangeEnd

Represents then end of range which should be generated for the partition function. The supported data types are the same as for the @rangeStart. See the @rangeStart for details.

The @rangeEnd is inclusive in the range, but may not be included in the final generated partition function in dependency on the @incrementValue and @incrementUnit parameters. See those parameters for details.

@boundaryType

Specifies the boundary type of the boundary values for the partition function. It can be either LEFT or RIGHT. It defines whether the boundary value is included in the left or right partition respective to the boundary value. Default value is RIGHT.

@incrementValue

Defines increment of the boundary values. This means that the @rangeStart value is incremented by the @incrementValue until @rangeEnd is reached.

@incrementUnit

Specifies the unit of the increment. It is being used only for range values representing date. The allowed units are YEAR, MONTH, WEEK, ISO_WEEK, DAY.

In each iteration step the @rangeStart value is incremented by the @incrementValue number of @incrementUnit.

Also the @incrementUnit may have impact on the @rangeStart value. If the @rangeStart value does not point to the first day of respective unit, it is being automatically shifted to the beginning of corresponding unit based on the @rangeStart value.

Samples of @rangeStart shifting:

@rangeStart @incrementUnit @rangeStart shifted to
'2016-03-17' YEAR '2016-01-01'
'2016-03-17' MONTH '2016-03-01'
'2016-03-17' WEEK '2016-03-14'
'2016-01-01' ISO_WEEK '2015-12-28'

@useIntegerDates

Specifies whether boundary values representing dates in the partition function should be represented as int date types or corresponding date, datetime or datetime2 data type. In case of  @useIntegerDates = 0 the boundary value is always first day of corresponding @incrementUnit.

In case of @useIntegerDates = 1 the boundary value is expressed as integer number and the value representing the range depends on the @integerFormatType parameter. See the @integerFormatType for details

It is being used only for date ranges. Default value is 1.

@integerFormatType

Specifies integer format of date range value. When the partition function is being generated and date ranges are being used, the range value is internally always represented by a first day of corresponding rage. if the @useIntegerDate = 1 the final boundary value is generated based on the format type.

Supported format types are:

FormatType Boundary value formatting
1 yyyyMMdd For example 20160101
2 yyyyxx(x) where xxx(x) correspond to appropriate month, week or day within particular year. For example 2016053 represents a day 53 of year 2016. 201643 represents week or iso_week 43 in year 2016

Default value is 2.

@printScriptOnly

Specifies whether only the partition function CREATE script is being printed or the partition function is being automatically crated.

The default value is 1, this means that only script is printed.

Samples

Below are some sample usages of the stored procedure.

Sample 1

sp_tblCreatePartitionFunction
    @pfName = 'myPf'
   ,@rangeStart = 1
   ,@rangeEnd = 1001
   ,@boundaryType = 'RIGHT'
   ,@incrementvalue = 100
CREATE PARTITION FUNCTION [myPf](int) AS RANGE RIGHT FOR VALUES (
    1
   ,101
   ,201
   ,301
   ,401
   ,501
   ,601
   ,701
   ,801
   ,901
   ,1001
)

Sample 2

sp_tblCreatePartitionFunction
    @pfName = 'myPf'
   ,@rangeStart = '2016-01-01'
   ,@rangeEnd = '2020-12-31'
   ,@boundaryType = 'RIGHT'
   ,@incrementvalue = 1
   ,@incrementUnit = 'YEAR'
   ,@useIntegerDates = 1
   ,@integerFormatType = 2

CREATE PARTITION FUNCTION [myPf](int) AS RANGE RIGHT FOR VALUES (
    2016
   ,2017
   ,2018
   ,2019
   ,2020
)

Sample 3

sp_tblCreatePartitionFunction
    @pfName = 'myPf'
   ,@rangeStart = '2016-01-01'
   ,@rangeEnd = '2016-12-31'
   ,@boundaryType = 'LEFT'
   ,@incrementvalue = 1
   ,@incrementUnit = 'MONTH'
   ,@useIntegerDates = 1
   ,@integerFormatType = 2
CREATE PARTITION FUNCTION [myPf](int) AS RANGE LEFT FOR VALUES (
    20160101
   ,20160201
   ,20160301
   ,20160401
   ,20160501
   ,20160601
   ,20160701
   ,20160801
   ,20160901
   ,20161001
   ,20161101
   ,20161201
)

Sample 4

sp_tblCreatePartitionFunction
    @pfName = 'myPf'
   ,@rangeStart = '2016-01-01'
   ,@rangeEnd = '2016-12-31'
   ,@boundaryType = 'LEFT'
   ,@incrementvalue = 1
   ,@incrementUnit = 'MONTH'
   ,@useIntegerDates = 0
CREATE PARTITION FUNCTION [myPf](datetime) AS RANGE LEFT FOR VALUES (
    '20160101'
   ,'20160201'
   ,'20160301'
   ,'20160401'
   ,'20160501'
   ,'20160601'
   ,'20160701'
   ,'20160801'
   ,'20160901'
   ,'20161001'
   ,'20161101'
   ,'20161201'
)

Getting Database Rights Assignment Overview – sp_HelpRights on GitHub

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

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

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

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

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

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

sp_HelpRights

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

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

Syntax

The syntax for the sp_HelpRights is quite sraighforward:

sp_HelpRights [parameters]

To display help for the stored procedure invoke

sp_HelpRights '?'

Parameters

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

@databases

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

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

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

@principals

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

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

sp_HelpRights Output

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

sp_helprights_sample1
sp_HelpRights output overview

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

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

Let’s look on the below output:

sp_helprights_sample2
Sample 1

 

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

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

sp_helprights_sample3
Sample 2

 

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

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

Summary

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

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

Any somments and/or suggestions are welcomed.

Clone / Copy SSIS Server Variables Among Environments – sp_SSISCloneEnvironment on GitHub

You have SSIS projects deployed in the SSISDB catalog. You are using environments and environment variables for passing parameter values to SSIS Executables. This is a very common scenario.

You come to a situation that you need transfer the SSIS projects, environments and varaibles among different SSIS environments (DEV/TEST/QA/PROD). You already have bunch of environment variables defined in one of your environment and you need to transfer those to other environment.

Unfortunately there is no easy way to clone, copy or script the varaibles to transfer them to different environment. If you do not script them during creation in the SSMS, there is no option in SSMS to script them out. If you do not want to type all the varaibles manually again, you have to script those in some way.

Anyway the proper scripting or clonning is not completely trivial in case sensitive varaible values are being used as SSISDB uses multiple symmetric keys and certificates for the encryption and each Environment in the SSISDB has its own key and corresponding certificate. When cloning such sensitive value, the value needs to be decrypted with the source environment symmetric key and re-encrypted by the destination environment symmetric key.

sp_SSISCloneEnvironment

sp_SSISCloneEnvironment is a custom stored procedure which takes care about cloning the SSSI server variables among different environments in the same SSISDB catalog as well as it allows generate a script, which can be re-applied on even oon other SSIS Instance.

The stored procedure takes care about sensitive values re-encryption by proper certificate when clonning. As mentioned above, SSIS generates a separate symmetric key and certificate for each environment. To ensure proper future functionality, the stored procedure takes care about decryption of the sensitive variables when reading from current environment and re-encryption by proper destination environment symmetric key.

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. Only in areas where it needs to create a folder or environment it switches the context to the caller of the stored procedure as the procedures which are taking care abut the folder and environment creation must be executed under 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 carefull, 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_SSISCloneEnvironment is available in my SQL-Scripts project on GitHub in the SSISDB folder.

The syntax of the stored procedure is as follow:

sp_SSISCloneEnvironment
   @sourceFolder                = 'Source Folder Name'
  ,@sourceEnvironment           = 'Source Environment Name'
  ,@destinationFolder           = 'Destination Folder Name'
  ,@destinationEnvironment      = 'Destination Environment Name'
  ,@autoCreate                  = 1 --Auto Create Destination
  ,@printScript                 = 1 --Specifies that Create Script should be output instead of actual execution
  ,@decryptSensitiveInScript    = 1 --Specifies that the sensitive values should be decrypted in the create script

Currently when cloning the environment variables the destination environment must be empty if script is not being generated and the procedure is running in the non-interactive mode.

The generated script is also easily reusable as there are two variables in the beginning for specification of the destination folder and environment. By simple modification of those variables the script will generate the variables different environments.

Parameters

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

The core parameters are @sourceFolder, @sourceEnvironment and @destinationFolder. The @destinationEnvornment, @autoCreate, @printScript and @decryptSensistiveInScript are optional.

@source Folder

Specifies the source folder where the source environment is located

@sourceEnvironment

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

@destinationFolder

Name of the destination folder to wich the environment varaibles should be clonned.

@destinationEnvironment

Name of the destination environment to which the varaibles should be conned. This parameter is optional and if not provided, then the name of the source environment is used.

@autoCreate

Specifies whether the destination foolde and destination environment should be automatically crated if they do not exist. If the script is being generated then a statement which checks existence of the folder and environment are included in the script and missing folder and/or environment is automatically created if needed by the script.

Default value is 1 = true

@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 clnes the variables

@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, howver 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 priorexecution of the script.

Sample Output

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

RAISERROR(N'sp_SSISCloneEnvironment v0.20 (2016-10-11) (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 @destinationEnvironment nvarchar(128) = N'ClonedEnvironment' --Specify Destination Environment Name

--Checking for destination folder existence
IF NOT EXISTS(SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE [name] = @destinationFolder)
BEGIN
    RAISERROR(N'Creating missing Folder [%s]', 0, 0, @destinationFolder) WITH NOWAIT;
    EXEC [SSISDB].[catalog].[create_folder] @folder_name = @destinationFolder
END

--Checking for destination environment existence
IF NOT EXISTS(
    SELECT 1
    FROM [SSISDB].[catalog].[environments] e
    INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = e.folder_id
    WHERE f.[name] = @destinationFolder AND e.[name] = @destinationEnvironment
)
BEGIN
    RAISERROR(N'Creating missing Environment [%s]\[%s]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT
    EXEC [SSISDB].[catalog].[create_environment] @folder_name = @destinationFolder, @environment_name = @destinationEnvironment, @environment_description = N''
END

--Checking for variables existence in destination eivnironment
IF EXISTS (
    SELECT 1
    FROM [SSISDB].[catalog].[environment_variables] ev
    INNER JOIN [SSISDB].[catalog].[environments] e ON e.environment_id = ev.environment_id
    INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = e.folder_id
    WHERE
        f.name = @destinationFolder AND e.name = @destinationEnvironment
)
BEGIN
    RAISERROR(N'Destination Environment [%s]\[%s] is not empty. Clear all variables prior clonning environment.', 15, 2, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
    RETURN;
END

--Environment variables creation
DECLARE @var sql_variant

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V1]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(bit, N'1');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V1', @data_type=N'Boolean', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V10]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'This is string parameters');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V10', @data_type=N'String', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V11]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'This is sensitive string'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V11', @data_type=N'String', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V12]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(datetime, N'2010-12-31T00:00:00'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V12', @data_type=N'DateTime', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V13]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(decimal(28, 18), N'99.990000000000000000'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V13', @data_type=N'Decimal', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V14]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(int, N'56'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V14', @data_type=N'Int32', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V2]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(tinyint, N'5');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V2', @data_type=N'Byte', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V3]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(datetime, N'2016-01-01T00:00:00');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V3', @data_type=N'DateTime', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V4]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(decimal(28, 18), N'12.390000000000000000');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V4', @data_type=N'Decimal', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V5]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(float, N'156.987');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V5', @data_type=N'Double', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V6]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(int, N'123456');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V6', @data_type=N'Int32', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V7]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(bigint, N'987654321');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V7', @data_type=N'Int64', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V8]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(smallint, N'9');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V8', @data_type=N'SByte', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V9]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(float, N'56');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V9', @data_type=N'Single', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

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.

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.