this script readls the roles for a database server and then creates directly a script that can be run against a different server in order to re-crate logins and roles.
-- SCRIPT TO CREATE THE ROLES CREATION SCRIPT
-- IN ORDER TO MOVE THE DATABASES TO ANOTHER SERVER
-- This script needs to be run on the original Server
-- and it will output the necessary SQL instructions
-- that you will run on the new server to replicate
-- the original server roles configuration
-- Originally found on http://www.sqlservercentral.com/Forums/Topic508244-359-1.aspx -- Fabrice Faleni
--Role Memberships'
DECLARE @DBName VARCHAR(64)
DECLARE c1 CURSOR FOR
SELECT name
FROM MASTER..sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN c1
FETCH NEXT FROM c1
INTO @DBName
WHILE @@FETCH_STATUS <> -1
BEGIN
PRINT @DBName
EXEC( 'USE ' + @DBName + ' GO;')
SELECT --rm.role_principal_id,
'EXEC sp_addrolemember @rolename =' + Space(1) + Quotename(User_name(rm.role_principal_id),'''') + ', @membername =' + Space(1) + Quotename(User_name(rm.member_principal_id),'''') AS '--Role Memberships'
FROM sys.database_role_members AS rm
ORDER BY rm.role_principal_id
--Object Level Permissions'
SELECT CASE
WHEN perm.state != 'W'
THEN perm.state_desc
ELSE 'GRANT'
END + Space(1) + perm.permission_name + Space(1) + 'ON ' + Quotename(Schema_name(obj.schema_id)) + '.' + Quotename(obj.name) COLLATE latin1_general_ci_as_ks_ws + CASE
WHEN cl.column_id IS NULL
THEN Space(0)
ELSE '(' + Quotename(cl.name) + ')'
END + Space(1) + 'TO' + Space(1) + Quotename(usr.name) + 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
ORDER BY usr.name
--Database Level Permissions'
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(usr.name) COLLATE database_default + CASE
(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 c1
INTO @dbname
END
CLOSE c1
DEALLOCATE c1
