Script To generate the script of Database Users, roles and object level permissions in SQL Server 2005

-- Script To generate the script of Database Users, roles and object level permissions in SQL Server 2005.

SET NOCOUNT ON

DECLARE     @OldUser varchar(100), @NewUser sysname

DECLARE a_cur CURSOR FOR

select name,name from sys.sysusers where uid>3 AND uid < 16384 ORDER BY

      1

Open a_cur

FETCH NEXT from a_cur into @OldUser,@NewUser

--SET @NewUser = @OldUser

WHILE (@@FETCH_STATUS = 0)

BEGIN

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

FETCH NEXT from a_cur into @OldUser,@NewUser

END

CLOSE a_cur

DEALLOCATE a_cur