We create a new stored procedure on any request for ALTER, by adding a suffix with "_V1","_V2", etc. In this way we can hold a backward compatibility between the application and the database.
That standards made us hold many old versions of the code. We started to use with ALTER SCHEMA [CanDel] TRANSFER to keep only the last 4 versions of the stored procedure. Other process(job) act as a garbage collector - if object left 3 months in "CanDel" schema it will be dropped.
But, once a while we need to do a rollback and to transfer one of the stored procedure back to the original schema.
We noticed that the old permissions were gone. I'm tiring to understand is this functionality is by design or something that was changed.
We using SQL Server 2022.
I've add a sample code to reproduce.
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET NOCOUNT ON;
GO
USE tempdb;
GO
/* Cleanup */
IF OBJECT_ID('dbo.USP_PermissionsTest') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.USP_PermissionsTest;
END;
IF OBJECT_ID('CanDel.USP_PermissionsTest') IS NOT NULL
BEGIN
DROP PROCEDURE CanDel.USP_PermissionsTest;
END;
GO
/* Create test role */
IF USER_ID('UDR_Test') IS NULL
CREATE ROLE [UDR_Test];
GO
/* Create dummy stored procedure */
IF OBJECT_ID('dbo.USP_PermissionsTest') IS NULL
BEGIN
EXEC('CREATE PROCEDURE dbo.USP_PermissionsTest AS');
END;
GO
/* grant permission to the stored procedure */
GRANT EXECUTE ON dbo.USP_PermissionsTest TO [UDR_Test];
GO
/* check permissions */
SELECT USER_NAME(p.grantee_principal_id) AS principal_name,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
CASE p.class_desc
WHEN 'DATABASE' THEN DB_NAME()
WHEN 'SCHEMA' THEN SCHEMA_NAME(p.major_id)
WHEN 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(p.major_id)
ELSE NULL
END AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
FROM sys.database_permissions p
INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT OUTER JOIN sys.all_objects o ON p.major_id = o.object_id
WHERE p.grantee_principal_id = USER_ID('UDR_Test')
AND o.object_id = OBJECT_ID('dbo.USP_PermissionsTest')
ORDER BY o.name;
GO
/* Create different schema to a destination */
IF SCHEMA_ID('CanDel') IS NULL
BEGIN
EXEC('CREATE SCHEMA [CanDel];');
END
GO
/* Transfer stored procedure to other schema */
ALTER SCHEMA [CanDel] TRANSFER dbo.USP_PermissionsTest;
GO
/* check permissions */
SELECT USER_NAME(p.grantee_principal_id) AS principal_name,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
CASE p.class_desc
WHEN 'DATABASE' THEN DB_NAME()
WHEN 'SCHEMA' THEN SCHEMA_NAME(p.major_id)
WHEN 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(p.major_id)
ELSE NULL
END AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
FROM sys.database_permissions p
INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT OUTER JOIN sys.all_objects o ON p.major_id = o.object_id
WHERE p.grantee_principal_id = USER_ID('UDR_Test')
AND o.object_id = OBJECT_ID('CanDel.USP_PermissionsTest')
ORDER BY o.name;
GO
/* Cleanup */
IF OBJECT_ID('dbo.USP_PermissionsTest') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.USP_PermissionsTest;
END;
IF OBJECT_ID('CanDel.USP_PermissionsTest') IS NOT NULL
BEGIN
DROP PROCEDURE CanDel.USP_PermissionsTest;
END;
GO
IF SCHEMA_ID('CanDel') IS NOT NULL
BEGIN
EXEC('DROP SCHEMA [CanDel];');
END
GO
I write a wrapper for transfer with permissions
CREATE OR ALTER PROCEDURE dbo.USP_TransferToSchemaWithPermissions
@I_SourceSchema sysname = 'dbo',
@I_SourceObject sysname = 'USP_PermissionsTest',
@I_TargetSchema sysname = 'CanDel'
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cmd NVARCHAR(MAX);
DECLARE @crlf VARCHAR(2) = CHAR(13) + CHAR(10);
SELECT @I_TargetSchema = REPLACE(REPLACE(@I_TargetSchema,']',''),'[','');
SELECT @cmd = CONCAT(N'USE [',ORIGINAL_DB_NAME(),'];',@crlf,
N'IF OBJECT_ID(@I_TargetSchema + ''.'' + @I_SourceObject) IS NULL',@crlf,
N'BEGIN',@crlf,
N' DECLARE @icmd NVARCHAR(MAX) = N'''';',@crlf,
N' IF SCHEMA_ID(''',@I_TargetSchema,''') IS NULL EXEC(''CREATE SCHEMA [',@I_TargetSchema,'];'');',@crlf,
N' SELECT @icmd += CONCAT(IIF(dp.class IN (1,3,6,24,25),''IF '' + CASE dp.class',@crlf,
N' WHEN 0 THEN ''''',@crlf,
N' WHEN 1 THEN',@crlf, --table or column subset on the table
N' CASE WHEN dp.major_id < 0 THEN ''OBJECT_ID(''''sys.'' + OBJECT_NAME(dp.major_id) + '''''')''',@crlf,
N' ELSE ''OBJECT_ID('''''' + (SELECT @I_TargetSchema + ''.'' + name FROM sys.objects WHERE object_id = dp.major_id)+ '''''')'' END',@crlf,
N' WHEN 3 THEN ''SCHEMA_ID('''''' + @I_TargetSchema + '''''')''',@crlf,
N' WHEN 6 THEN ''TYPE_ID('''''' + (SELECT name FROM sys.types WHERE user_type_id = dp.major_id) + '''''')''',@crlf,
N' WHEN 24 THEN ''KEY_ID('''''' + (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id = dp.major_id) + '''''')''',@crlf,
N' WHEN 25 THEN ''CERT_ID('''''' + (SELECT name FROM sys.certificates WHERE certificate_id = dp.major_id) + '''''')''',@crlf,
N' END COLLATE DATABASE_DEFAULT + '' IS NOT NULL '',''''),',@crlf,
N' CASE dp.state',@crlf,
N' WHEN ''D'' THEN ''DENY ''',@crlf,
N' WHEN ''G'' THEN ''GRANT ''',@crlf,
N' WHEN ''R'' THEN ''REVOKE ''',@crlf,
N' WHEN ''W'' THEN ''GRANT ''',@crlf,
N' END,dp.permission_name,'' '',',@crlf,
N' CASE dp.class',@crlf,
N' WHEN 0 THEN ''''',@crlf,
N' WHEN 1 THEN',@crlf, --table or column subset on the table
N' CASE WHEN dp.major_id < 0 THEN',@crlf,
N' + ''ON [sys].['' + OBJECT_NAME(dp.major_id) + ''] ''',@crlf,
N' ELSE',@crlf,
N' + ''ON ['' +',@crlf,
N' (SELECT @I_TargetSchema + ''].['' + name FROM sys.objects WHERE object_id = dp.major_id)',@crlf,
N' +',@crlf, -- optionally concatenate column names
N' CASE WHEN MAX(dp.minor_id) > 0 ',@crlf,
N' THEN ''] (['' + REPLACE(',@crlf,
N' (SELECT name + ''], ['' ',@crlf,
N' FROM sys.columns ',@crlf,
N' WHERE object_id = dp.major_id ',@crlf,
N' AND column_id IN (SELECT minor_id ',@crlf,
N' FROM sys.database_permissions ',@crlf,
N' WHERE major_id = dp.major_id',@crlf,
N' AND USER_NAME(grantee_principal_id) IN (USER_NAME(dp.grantee_principal_id))',@crlf,
N' )',@crlf,
N' FOR XML PATH('''')',@crlf,
N' )',@crlf, --replace final square bracket pair
N' + ''])'', '', []'', '''')',@crlf,
N' ELSE '']''',@crlf,
N' END + '' ''',@crlf,
N' END',@crlf);
SELECT @cmd += CONCAT(
N' END COLLATE DATABASE_DEFAULT,''TO ['',USER_NAME(dp.grantee_principal_id),'']'',',@crlf,
N' CASE dp.state WHEN ''W'' THEN '' WITH GRANT OPTION'' ELSE '''' END,'';'',@crlf)',@crlf,
N' FROM sys.database_permissions dp',@crlf,
N' WHERE dp.major_id = OBJECT_ID(@I_SourceSchema + ''.'' + @I_SourceObject)',@crlf,
N' GROUP BY dp.state, dp.major_id, dp.permission_name, dp.class,dp.grantee_principal_id;',@crlf,
N' ',@crlf,
N' ALTER SCHEMA [',@I_TargetSchema,'] TRANSFER [',@I_SourceSchema,'].[',@I_SourceObject,'];',@crlf,
N' EXEC sys.sp_executesql @icmd, N''@crlf VARCHAR(2),@I_SourceSchema sysname,@I_SourceObject sysname,@I_TargetSchema sysname'',',@crlf,
N' @crlf = @crlf,@I_SourceSchema = @I_SourceSchema, @I_SourceObject= @I_SourceObject, @I_TargetSchema = @I_TargetSchema;',@crlf,
N'END',@crlf);
EXEC sys.sp_executesql @cmd, N'@crlf VARCHAR(2),@I_SourceSchema sysname,@I_SourceObject sysname,@I_TargetSchema sysname',
@crlf = @crlf,@I_SourceSchema = @I_SourceSchema, @I_SourceObject= @I_SourceObject, @I_TargetSchema = @I_TargetSchema;
END
GO