Sunday, January 2, 2011

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
   CREATE PROC dbo.usp_CopyPerms (
    @dbname VARCHAR(35)
   /* 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


Deny Execute ON procedureName TO userName;   
Grant Execute ON procedureName TO userName;   
Grant Insert ON tableName TO userName;   
Grant Update ON tableName TO userName;   

No comments:

Post a Comment