Home Forums SQL Server 7,2000 Administration I need to Copy/refresh the "data only" to Test from prod RE: I need to Copy/refresh the "data only" to Test from prod

  • Instead of SSIS, google this:

    sp_help_revlogin

    The output from that will produce output to create the users.

    This code will generate code to create the users and add them to the proper server roles:

    SELECT 'EXEC sp_addsrvrolemember @rolename = ' + QUOTENAME(usr1.name, '') + ', @loginame = ' + QUOTENAME(usr2.name, '')

    FROM sys.server_principals usr1

    INNER JOIN sys.server_role_members rm ON usr1.principal_id = rm.role_principal_id

    INNER JOIN sys.server_principals usr2 ON rm.member_principal_id = usr2.principal_id

    As for the permissions inside each database, go to Books Online and look up sp_helprolemember. That can help you create a set of scripts to get you started.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/