Transfer permissions between objects in SQL Server 2008
Sometimes we have to transfer the permissions from one object to another. Here the object could be a table or view. We can write a straight forward script using GRANT and REVOKE statements if this requirement is for one or two objects. But what if more number of objects are involved and the permissions that exist at the time of development might change by the time you deploy the code in production. What I mean is, if you get a list of permissions that already exist on these tables from production and create scripts based on that, there is no guarantee that the same permissions exist when this script goes to production. Obviously, this results in the failure of the script. So, we need an intelligent script rather than a static script in this scenario.
Let us consider a scenario in which a bunch of tables are there and access to these tables by down stream systems is controlled by some database roles. Now, these tables have to be renamed due to some business reason without breaking the downstream systems. What we typically do in this case is, rename the tables using sp_rename and create views with the old table names on top of the renamed tables. The backward compatibility is maintained as we are creating the views with old table names. The permissions given to the database roles on the old table names will be automatically carried over to the newly renamed tables as a part of the renaming itself. But downstream systems still cannot access the views because the permissions exist on the newly renamed tables have to be explicitly transferred to the views. This is where the need comes for a dynamic script that does the permissions transfer from one object to another.
So, here is a generic script that would come handy in this type of scenarios. This SP transfers the permissions from an object (@FromObjectName parameter) to another object (@ToObjectName parameter). Appreciate your feedback if you feel this is useful.
CREATE PROC [dbo].[ResetPermsOnObject] (
@DBName sysname,
@SchemaName sysname = 'dbo',
@ToObjectName sysname,
@FromObjectName sysname
)
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON
DECLARE @ObjectName sysname
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @SQL nvarchar(4000)
DECLARE @RetVal Int
DECLARE @CurCount Int
DECLARE @MaxCount Int
DECLARE @Grantee sysname
DECLARE @Action NVarchar(20)
DECLARE @PermList Table
(
Id Int IDENTITY(1,1),
[Owner] sysname,
[Object] sysname,
Grantee sysname,
Gantor sysname,
ProtectType NVarchar(10),
[Action] NVarchar(20),
Grantor sysname
)
-------------Validate arguments----------------------
IF(@DBName IS NULL)
BEGIN
SELECT @ErrorMessage = 'Database Name must be supplied.'
GOTO ABEND
END
IF(@ToObjectName IS NULL)
BEGIN
SELECT @ErrorMessage = 'Old Object Name must be supplied.'
GOTO ABEND
END
IF(@FromObjectName IS NULL)
BEGIN
SELECT @ErrorMessage = 'New Object Name must be supplied.'
GOTO ABEND
END
--Check for the existence of the Database
IF NOT EXISTS(SELECT Name FROM sys.databases where Name =@DBName)
BEGIN
SET @ErrorMessage = 'The specified Database does not exist'
GOTO ABEND
END
--Check for the existence of the Schema
IF(upper(@SchemaName) <> 'DBO')
BEGIN
SET @SQL = 'SELECT @RetVal = COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.schemas WHERE name = ''' + @SchemaName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Bit OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH
IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No Schema with the name ' + @SchemaName + ' exists in the Database ' + @DBName
GOTO ABEND
END
END
-------------Check for the validity of all the Objects----------------------
--Check for existence of the Old object
SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Objects WHERE name = ''' + @ToObjectName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH
IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No object with the name ' + @ToObjectName + ' exists in the Database ' + @DBName
GOTO ABEND
END
--Check for existence of the New object
SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Objects WHERE name = ''' + @FromObjectName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH
IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No object with the name ' + @FromObjectName + ' exists in the Database ' + @DBName
GOTO ABEND
END
-------Check whether any DBRoles are given permissions or not --------------------------------------------
SET @SQL = 'SELECT @RetVal = COUNT(sdp.name) FROM ' + QUOTENAME(@DBName) + '.sys.sysprotects sp '
+ ' INNER JOIN ' + QUOTENAME(@DBName) + '.sys.sysobjects so ON sp.id = so.id '
+ ' INNER JOIN ' + QUOTENAME(@DBName) + '.sys.database_principals sdp ON sdp.Principal_Id = sp.uid'
+ ' WHERE so.Name = ''' + @FromObjectName + ''''
EXEC sp_executesql @SQL,N'@RetVal Bit OUTPUT',@RetVal OUTPUT
IF(@RetVal <= 0)
BEGIN
PRINT 'No roles are granted permissions on the object ' + @FromObjectName
GOTO FINAL
END
----------Get the permissions defined on the old object------------------------------------------------------
SET @SQL = 'Use ' + QUOTENAME(@DBName) + '; EXEC sp_helprotect ' + QUOTENAME(@SchemaName + '.' + @FromObjectName)
INSERT INTO @PermList
EXEC sp_executesql @SQL
----Now loop through all the roles and assign the permissions to the old Object ------------------------------
SELECT @CurCount = Min(Id),@MaxCount = Max(Id) FROM @PermList
WHILE(@CurCount <= @MaxCount)
BEGIN
SELECT @Action = [Action], @Grantee = Grantee FROM @PermList WHERE Id = @CurCount
SET @SQL = 'USE ' + QUOTENAME(@DBName) + ';GRANT ' + @Action + ' ON OBJECT::' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ToObjectName) + ' TO ' + QUOTENAME(@Grantee)
EXEC(@SQL)
IF(@@ERROR <> 0)
BEGIN
SET @ErrorMessage = 'Unbale to grant permissions on ' + QUOTENAME(@ToObjectName) + ' to the role ' + QUOTENAME(@Grantee)
GOTO ABEND
END
SET @SQL = 'USE ' + QUOTENAME(@DBName) + ';REVOKE ' + @Action + ' ON OBJECT::' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@FromObjectName) + ' FROM ' + QUOTENAME(@Grantee)
EXEC (@SQL)
IF(@@ERROR <> 0)
BEGIN
SET @ErrorMessage = 'Unbale to revoke permissions on Object ' + QUOTENAME(@ToObjectName) + ' from the role ' + QUOTENAME(@Grantee)
GOTO ABEND
END
PRINT 'Permissions are successfully applied on ' + QUOTENAME(@ToObjectName) + ' TO the role ' + QUOTENAME(@Grantee)
SET @CurCount = @CurCount + 1
END
RETURN
ABEND:
RAISERROR 500001 @ErrorMessage
FINAL:
RETURN
END