Copy Object Permissions

How many times have you needed to compare object permissions between instances, because you've found the security definition isn't what you expected?  Or, maybe you have build a new instance, and you're just copying over permissions to ensure everything is in synch.  I've posted a quick procedure here which will return your permissions per object and user, along with the necessary privilege action (ie GRANT).  You can copy the output to another instance, and run it, as needed.

   IF OBJECT_ID('usp_CopyPerms','p')>0

   DROP PROC dbo.usp_CopyPerms

   GO

   CREATE PROC dbo.usp_CopyPerms (

    @dbname VARCHAR(35)

   )

   AS

   SET NOCOUNT ON;

   /* Outputs object permissions, per user, to be copied/run on another instance.

   EXEC dbo.usp_CopyPerms @dbname = 'DatabaseName'  */

   DECLARE @perms table (

     ObjectOwner VARCHAR(50) NULL,

     ObjectName VARCHAR(50) NULL,

     Grantee VARCHAR(50) NULL,

     Grantor  VARCHAR(50) NULL, 

     ProtectType VARCHAR(50) NULL,

     [Privilege] VARCHAR(50) NULL,

     [Column] VARCHAR(10))

   INSERT @perms

   EXEC ('EXEC ' + @DBName + '.dbo.sp_helprotect')

   SELECT LTRIM(RTRIM(ProtectType))+' '+LTRIM(RTRIM(Privilege))+' '+' ON '+[ObjectName]+' TO    '+Grantee+';

   ' FROM @perms

   WHERE ObjectOwner NOT IN('sys','.')

   AND ObjectName NOT LIKE '%OLD'

   AND ObjectName NOT LIKE '%TMP'

   ORDER BY ObjectName,Grantee

   SET NOCOUNT OFF;

   GO

EXAMPLE OUTPUT:
Deny Execute ON procedureName TO userName;   
Grant Execute ON procedureName TO userName;   
Grant Insert ON tableName TO userName;   
Grant Update ON tableName TO userName;