Script object or user permissions

,

script useful for copying permissions from one database to another like test to production.

Tim Stahlhut

SEE sp_helprotect for params meaning

Note: If you wish to script system object permission remove 'IsMSShipped' block of code.

Fixed missing code & re-formatted it to look better.

SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS  ON 
GO

IF OBJECT_ID('dbo.usp_script_permissions') IS NULL
    EXEC  ('CREATE PROC dbo.usp_script_permissions
        AS RAISERROR ( ''Dummy Proc Called'', 16, 62)')   
GO

ALTER PROC dbo.usp_script_permissions
( 	
    @name               ncharacter varying(776)     = NULL,
    @username           sysname                     = NULL,
    @grantorname        sysname                     = NULL,
    @permissionarea     character varying(10)       = 'o s'
 )AS
/*

by Tim Stahlhut Email stahta01 at netzero dot com

Scripts code to grant permissions on user objects
@name object name like dbo.table or dbo.proc
see sp_helprotect for more info on params

*/ 
 BEGIN

    SET NOCOUNT ON

    CREATE Table #tmp_protect_data ( OwnerName sysname NOT Null, 
        ObjectName  sysname NOT Null, GranteeName sysname NOT Null, 
        GrantorName sysname NOT Null, ProtectType char(10) Null, 
        ActionName varchar(20) Null, ColumnName sysname Null)
        
    SET @username = PARSENAME (  @username , 1 ) -- Remove []     

    insert into #tmp_protect_data (OwnerName, ObjectName,  
      GranteeName, GrantorName, ProtectType, ActionName, ColumnName )  
    exec sp_helprotect @name =  @name, @username = @username, 
      @grantorname = @grantorname, @permissionarea = @permissionarea
        
    SELECT RTRIM(ProtectType) + ' ' + ActionName + 
       CASE 
          WHEN ColumnName IN ('.','(All+New)','(All)','(New)') THEN ''
          ELSE ' (' + ColumnName + ')'
       END +
       ' ON ' + OwnerName + '.' + ObjectName + ' TO ' + 
        GranteeName + char(13)+char(10)
    FROM  #tmp_protect_data
    --  ColumnName can be NULL on deleted columns
    WHERE ColumnName IS NOT NULL AND 
      OBJECTPROPERTY ( OBJECT_ID(OwnerName + '.' + ObjectName), 
          'IsMSShipped' ) = 0
    ORDER BY OwnerName + '.' + ObjectName, 
            GranteeName, ActionName, RTRIM(ProtectType)

    DROP Table #tmp_protect_data

 END

/*** Test / Example Code
    exec sp_helprotect @username = 'public'
    exec dbo.usp_script_permissions @username = 'public'
    exec dbo.usp_script_permissions @name = '[dbo].[usp_script_permissions]'
***/


GO

GRANT  EXECUTE  ON dbo.usp_script_permissions  TO admin -- SQL Role
GO

Rate

Share

Share

Rate