copying users between roles

I would like to copy all users currently from RoleA into RoleB. I know there
must be a nice way to script this. Any ideas?Robert
There is no a "nice" script to move users from one Role to another at least
I am not aware.
You will have to deal with system tables that hold the info about users but
it is not recommended.
"Robert Kinesta" <RobertKinesta@.discussions.microsoft.com> wrote in message
news:66EA482B-0FD9-49A9-B049-6CADCA5B5BD1@.microsoft.com...
>I would like to copy all users currently from RoleA into RoleB. I know
>there
> must be a nice way to script this. Any ideas?|||Hi Robert,
This may help u......a nice script to move all users from roleA to roleB
create table #users (
UserName sysname,
GroupName sysname,
LoginName sysname,
DefDBName sysname,
UserID smallint,
SID smallint
)
insert #users
exec('sp_helpuser')
select identity(int,1,1) as idn, * into #usersInRoleA from #users where
GroupName like 'roleA'
declare @.i as int,
@.maxusers as int,
@.username as sysname
select @.maxusers = max(idn) from #usersInRoleA
set @.i = 1
while (@.i <= @.maxusers)
begin
select @.username = username from #usersInRoleA where idn = @.i
exec sp_addrolemember 'roleB',@.username
exec sp_droprolemember 'roleA',@.username
set @.i = @.i + 1
end
drop table #usersInRoleA
drop table #users
enjoy and keep going...
"Robert Kinesta" wrote:

> I would like to copy all users currently from RoleA into RoleB. I know the
re
> must be a nice way to script this. Any ideas?|||Hi Robert
Maybe something like:
CREATE TABLE #Rolemembers ( DbRole sysname,
MemberName sysname,
MemberSID varbinary(85) )
INSERT INTO #Rolemembers ( DbRole, MemberName, MemberSID )
EXEC sp_helprolemember 'ISDLOAD_ROLE'
DECLARE @.member sysname
DECLARE Member_Cursor CURSOR FOR SELECT MemberName FROM #Rolemembers
OPEN Member_Cursor
FETCH NEXT FROM Member_Cursor INTO @.member
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_executesql N'EXEC sp_addrolemember @.rolename= ''ROLEB'' ,
@.membername = @.membernm' ,
N'@.membernm sysname',
@.membernm = @.member
FETCH NEXT FROM Member_Cursor INTO @.member
END
CLOSE Member_Cursor
DEALLOCATE Member_Cursor
DROP TABLE #Rolemembers
John
"Robert Kinesta" wrote:

> I would like to copy all users currently from RoleA into RoleB. I know the
re
> must be a nice way to script this. Any ideas?