/*
Santomieri Systems
Josh Santomieri
http://www.santsys.com/

Output: 
	SQL Statements to duplacate all roles in a database.
	
	Each role is output with a corresponding sp_addrole statement to create the role
	and a list of GRANT and DENY statements to set all table permissions.
*/

DECLARE @newName VARCHAR(100)
DECLARE @roleName VARCHAR(100)
DECLARE @permName VARCHAR(500)

declare cur CURSOR for 
(
	select 'RoleName' = name 
	from sys.database_principals 
	where (type = 'R' or type = 'A') 
		AND principal_id > 0 
		and principal_id < 16000
)

open cur
fetch next from cur into @roleName
while @@fetch_status = 0
begin 
	SET @newName = @roleName

	print '/********************************************************'
	print '                  ' + @newName
	print '********************************************************/'

	print 'EXEC sp_addrole ''' + @newName + ''''
	
	declare cPerm cursor for
	(
		select 
			p.state_desc + SPACE(1) + p.permission_name + SPACE(1) + 'ON [dbo].' + QUOTENAME(o.NAME) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@newName) COLLATE database_default as [permission]
		FROM
			sys.database_permissions p 
			LEFT OUTER JOIN sys.all_objects o ON p.major_id = o.OBJECT_ID
			INNER JOIN sys.database_principals dp ON  p.grantee_principal_id = dp.principal_id
		WHERE 
			dp.type_desc = 'DATABASE_ROLE'	
			AND dp.NAME = @roleName
	)
	
	open cPerm
	fetch next from cPerm into @permName
	while @@fetch_status = 0
	begin
		print @permName
		fetch next from cPerm into @permName
	end
	deallocate cPerm

fetch next from cur into @roleName
end 

deallocate cur