• 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]