Re: Copy role members to another database role - How to?

Thanks a million Dan,
meanwhile with some BOL search I was able
to build as similar script:

....
Open role_Cursor
FETCH NEXT FROM role_Cursor INTO @membername
WHILE @@FETCH_STATUS = 0
BEGIN
Print @membername
EXEC sp_addrolemember 'MyDatabaseRole' , @membername
FETCH NEXT FROM role_Cursor INTO @membername
END
CLOSE role_Cursor
DEALLOCATE role_Cursor


Again many thanks for responding!

Bodo


"Dan Guzman" wrote:

I'm looking for a T-SQL script that allows me loop through all members of
an existing database role and copy each member into another database role
of the same database.

Can anyone help with a sample code?

Below is a sample script:

DECLARE
@SourceRole sysname = 'SourceRole',
@RoleMember sysname;
DECLARE RoleMembers CURSOR LOCAL STATIC FOR
SELECT
member.name AS member_name
FROM sys.database_principals AS role
JOIN sys.database_role_members AS role_member ON
role_member.role_principal_id = role.principal_id
JOIN sys.database_principals AS member ON
member.principal_id = role_member.member_principal_id WHERE
role.type = 'R'
AND role.name = @SourceRole;
OPEN RoleMembers;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM RoleMembers INTO @RoleMember;
IF @@FETCH_STATUS = -1 BREAK;
EXEC sp_addrolemember 'TargetRole', @RoleMember;
END;
CLOSE RoleMembers;
DEALLOCATE RoleMembers;


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


.