\SQL\Aeries SQL
Create Role SQL.sql
/* 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 left(name, 3) <> 'db_' ) 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