use AdventureWorks2008R2
go

declare @Mode varchar(8)
set @Mode='audit' --valid modes are apply and audit. Audit mode returns permissions differences. Apply mode updates permissions.

declare @PermissionsXML xml, @ObjectName nvarchar(128), @PermName nvarchar(128), @RoleName nvarchar(128), @ActionName nvarchar(128), @Cmd nvarchar(4000)

-- The below XML can have a full list of permissions between objects and roles. The sample below uses two roles, TestRole and ExposedRole, and works in the AdventureWorks2008R2 database.
set @PermissionsXML=N'<permissions>
<action name="grant">
	<perm object-name="Person.Person" perm-name="SELECT" role-name="TestRole" />
	<perm object-name="Person.Address" perm-name="DELETE" role-name="TestRole" />
</action>
<action name="deny">
	<perm object-name="Purchasing.Vendor" perm-name="UPDATE" role-name="ExposedRole" />
	<perm object-name="Production.TransactionHistory" perm-name="INSERT" role-name="ExposedRole" />
</action>
</permissions>'



if @Mode='audit'
begin

select
	case 
		when x.ObjectName is not null
			and p.name is null 
			then 'Failure, permission expected but does not exist'
		when p.name is not null
			and x.ObjectName is null
			then 'Failure, permission exists but is not expected'
		else 'Success, permission exists and is expected'
		end as Status
	,x.ObjectName as ExpectedObjectName
	,OBJECT_NAME(dp.major_id) as ActualObjectName
	,dp.major_id as ActualObjectID
	,dp.class_desc as ActualObjectType
	,x.ActionName as ExpectedPermission
	,dp.permission_name as ActualPermission
	,x.RoleName as ExpectedRole
	,p.name as ActualRole
	,x.ActionName as ExpectedAction
	,dp.state_desc as ActualAction
from
(
	select
		p.i.value('@object-name','nvarchar(128)') as ObjectName
		,p.i.value('@perm-name','nvarchar(128)') as PermName
		,p.i.value('@role-name','nvarchar(128)') as RoleName
		,p.i.value('../@name','nvarchar(128)') as ActionName
	from @PermissionsXML.nodes('permissions/action/perm') p(i)
) as x
full outer join sys.database_permissions dp
	on x.ActionName=dp.state_desc
	and x.PermName=dp.permission_name
	and OBJECT_ID(x.ObjectName)=dp.major_id
left outer join sys.database_principals p
	on p.principal_id=dp.grantee_principal_id
	and p.is_fixed_role <> 1 --ignore built-in roles, like db_owner or public
where dp.major_id is null or dp.major_id > 0

end

if @Mode='apply'
begin
	declare lCsr cursor local read_only
	for
	select
		p.i.value('@object-name','nvarchar(128)') as ObjectName
		,p.i.value('@perm-name','nvarchar(128)') as PermName
		,p.i.value('@role-name','nvarchar(128)') as RoleName
		,p.i.value('../@name','nvarchar(128)') as ActionName
	from @PermissionsXML.nodes('permissions/action/perm') p(i)
	where p.i.value('../@name','nvarchar(128)') in ('grant','deny','revoke') --you can only do these actions

	open lCsr

	fetch next from lCsr into @ObjectName, @PermName, @RoleName, @ActionName

	while @@FETCH_STATUS=0
	begin
		if object_id(@ObjectName) is null
		begin
			print 'Object '+@ObjectName+ ' not found.'
		end
		
		if not exists
		(
		select *
		from sys.database_principals
		where name=@RoleName
		)
		begin
			print 'Role '+@RoleName+' not found.'
		end

		if exists
		(
		select *
		from sys.database_principals
		where name=@RoleName
		)
		and object_id(@ObjectName) is not null
		begin
			set @Cmd=+@ActionName+' '+@PermName+' ON '+@ObjectName+' TO '+@ObjectName
			print @Cmd
			
			exec (@Cmd) --this is why this script is in a cursor
		end

		fetch next from lCsr into @ObjectName, @PermName, @RoleName, @ActionName
	end

	close lCsr
	deallocate lCsr
end