Share via


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