How to Script permissions using catalog views in SQL Server 2005

This script queries the following SQL Server 2005 security catalog views:

sys.database_role_members: This catalog view maps database users to database roles that they are members of  sys.database_permissions: Contains information about all the permissions held by users and roles sys.objects: Contains information about all user-defined database objects sys.database_principals: Contains information about all database users and database roles sys.columns: Contains data about each column of an object that has columns, such as views or tables

To use the below script, you will have to change the values of the @OldUser and @NewUser to the name of 'the user or role from which to copy the permissions from' 

For example, to script out the role ‘db_procexec’,change @olduser and @newuser to ‘DB_PROCEXEC’

 SET NOCOUNT ON

DECLARE    @OldUser sysname, @NewUser sysname 

SET           @OldUser = 'HRUser'

SET           @NewUser = 'PersonnelAdmin' 

SELECT      'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context' 

SELECT      '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment' 

SELECT      'EXEC sp_addrolemember @rolename ='

               + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'

FROM        sys.database_role_members AS rm

WHERE      USER_NAME(rm.member_principal_id) = @OldUser

ORDER BY rm.role_principal_id ASC 

SELECT      CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

               + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)

               + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

               + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

               + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM        sys.database_permissions AS perm

               INNER JOIN

               sys.objects AS obj

               ON perm.major_id = obj.[object_id]

               INNER JOIN

               sys.database_principals AS usr

               ON perm.grantee_principal_id = usr.principal_id

               LEFT JOIN

               sys.columns AS cl

               ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

WHERE      usr.name = @OldUser

ORDER BY perm.permission_name ASC, perm.state_desc ASC 

SELECT      CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

               + SPACE(1) + perm.permission_name + SPACE(1)

               + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

               + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'

FROM        sys.database_permissions AS perm

               INNER JOIN

               sys.database_principals AS usr

               ON perm.grantee_principal_id = usr.principal_id

WHERE      usr.name = @OldUser

AND          perm.major_id = 0

ORDER BY perm.permission_name ASC, perm.state_desc ASC