Thanks! I may need to get back into that.
For now, I used some of your code in the previous article and modified it. I am trying to copy Roles (and some users) from one DB to another on the same server. Here's what I got to work for me (this is a SQL 2000 server):
[font="Courier New"]---------------------------------------------------------------
-- COPY USERS & ROLES
--from 1 db on this server to another db on this server
---------------------------------------------------------------
-----------------------
-- change all CA_AugME to "from" DataBase(OLD db)
-- change all CA_SeptME to "to" DataBase(NEW db)
--
-- If the user had no permissions to DB, this will copy that from the "from" db and
-- print a msg in grid 'ADDED USER ', @user_name
--
-- All Roles on the "from" db will be applied to the "to" db
-------------------------------------------------------
-- based on code from http://www.sqlservercentral.com/scripts/T-SQL+Aids/30754/
-------------------------------------------------------
-- Copy Roles
USE CA_SeptME
declare @S varchar(1000)
DECLARE c_from_user_roles CURSOR
READ_ONLY
FOR
select u.name as UserName, a_ROLE.name as RoleName --, *
from CA_AugME.dbo.sysusers as u
join CA_AugME.dbo.sysmembers AS Person
on u.uid = Person.memberuid
JOIN CA_AugME.dbo.sysusers AS a_ROLE
on a_ROLE.uid = groupuid
WHERE U.NAME + A_ROLE.NAME
NOT IN (SELECT U.NAME + A_ROLE.NAME
from CA_SeptME.dbo.sysusers as u
join CA_SeptME.dbo.sysmembers AS Person
on u.uid = Person.memberuid
JOIN CA_SeptME.dbo.sysusers AS a_ROLE
on a_ROLE.uid = groupuid)
--AND U.NAME = 'jduffy'-- TEST only !
DECLARE @role_name varchar(40)
DECLARE @user_name varchar(40)
OPEN c_from_user_roles
FETCH NEXT FROM c_from_user_roles INTO @user_name, @role_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
BEGIN
if NOT exists (select * from CA_SeptME.dbo.sysusers where name = @user_name)
begin
exec sp_grantdbaccess @user_name-- add user to DB
-- print 'ADDED USER ' @user_name
SELECT 'ADDED USER ', @user_name
end
END
BEGIN
set @S = 'exec sp_addrolemember ''' + @role_name + ''', ''' + @user_name + ''''
print @S
exec(@s)-- copy Role for user
END
END
FETCH NEXT FROM c_from_user_roles INTO @user_name, @role_name
END
CLOSE c_from_user_roles
DEALLOCATE c_from_user_roles[/font]