Hi,

Try out this script and run it on your database. It give the permissions of the users as output. copy it and run it on your destination DB.

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

~KKK