"Lontae Jones" wrote:

> I often have to copy an entire database from production server onto a staging
> server.  I was looking on other ways this can be done without headaches. 
> This is what I do.  There are probably better ways out there.
>
> 1.  Perfrom Full backup of the database I need to copy.
> 2.  Use this command to restore to my new location. 
>
> Restore FilelistOnly From Disk = 'E:\NCR\NCR'
>
> Restore Database NCR from Disk = 'E:\NCR\NCR'
> With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
> Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
>
> 3.  Run the following script to create this stored proc sp_help_revlogin
>
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
>   DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
>     @binvalue varbinary(256),
>     @hexvalue varchar(256) OUTPUT
> AS
> DECLARE @charvalue varchar(256)
> DECLARE @i int
> DECLARE @length int
> DECLARE @hexstring char(16)
> SELECT @charvalue = '0x'
> SELECT @i = 1
> SELECT @length = DATALENGTH (@binvalue)
> SELECT @hexstring = '0123456789ABCDEF'
> WHILE (@i <= @length)
> BEGIN
>   DECLARE @tempint int
>   DECLARE @firstint int
>   DECLARE @secondint int
>   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
>   SELECT @firstint = FLOOR(@tempint/16)
>   SELECT @secondint = @tempint - (@firstint*16)
>   SELECT @charvalue = @charvalue +
>     SUBSTRING(@hexstring, @firstint+1, 1) +
>     SUBSTRING(@hexstring, @secondint+1, 1)
>   SELECT @i = @i + 1
> END
> SELECT @hexvalue = @charvalue
> GO
>
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
>   DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
> DECLARE @name    sysname
> DECLARE @xstatus int
> DECLARE @binpwd  varbinary (256)
> DECLARE @txtpwd  sysname
> DECLARE @tmpstr  varchar (256)
> DECLARE @SID_varbinary varbinary(85)
> DECLARE @SID_string varchar(256)
>
> IF (@login_name IS NULL)
>   DECLARE login_curs CURSOR FOR
>     SELECT sid, name, xstatus, password FROM master..sysxlogins
>     WHERE srvid IS NULL AND name <> 'sa'
> ELSE
>   DECLARE login_curs CURSOR FOR
>     SELECT sid, name, xstatus, password FROM master..sysxlogins
>     WHERE srvid IS NULL AND name = @login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
> IF (@@fetch_status = -1)
> BEGIN
>   PRINT 'No login(s) found.'
>   CLOSE login_curs
>   DEALLOCATE login_curs
>   RETURN -1
> END
> SET @tmpstr = '/* sp_help_revlogin script '
> PRINT @tmpstr
> SET @tmpstr = '** Generated '
>   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
> PRINT @tmpstr
> PRINT ''
> PRINT 'DECLARE @pwd sysname'
> WHILE (@@fetch_status <> -1)
> BEGIN
>   IF (@@fetch_status <> -2)
>   BEGIN
>     PRINT ''
>     SET @tmpstr = '-- Login: ' + @name
>     PRINT @tmpstr
>     IF (@xstatus & 4) = 4
>     BEGIN -- NT authenticated account/group
>       IF (@xstatus & 1) = 1
>       BEGIN -- NT login is denied access
>         SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
>         PRINT @tmpstr
>       END
>       ELSE BEGIN -- NT login has access
>         SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
>         PRINT @tmpstr
>       END
>     END
>     ELSE BEGIN -- SQL Server authentication
>       IF (@binpwd IS NOT NULL)
>       BEGIN -- Non-null password
>         EXEC sp_hexadecimal @binpwd, @txtpwd OUT
>         IF (@xstatus & 2048) = 2048
>           SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
>         ELSE
>           SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
>         PRINT @tmpstr
>     EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
>         SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
>           + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
>       END
>       ELSE BEGIN
>         -- Null password
>     EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
>         SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
>           + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
>       END
>       IF (@xstatus & 2048) = 2048
>         -- login upgraded from 6.5
>         SET @tmpstr = @tmpstr + '''skip_encryption_old'''
>       ELSE
>         SET @tmpstr = @tmpstr + '''skip_encryption'''
>       PRINT @tmpstr
>     END
>   END
>   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
>   END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
>
> 4.  Then I run that sp (sp_help_revlogin) and copy that data to my new
> server to fix the users.
>
> The problem I have is that the users logins all need to be fixed  I will run
> this to fix them
> use DatabaseName
> go
> sp_change_users_login Auto_Fix, 'user'
> go
>
> Sometimes this doesnt work.  Get the users from one server to another is my
> biggest problem.  Pleassssssse Help.  Thanks alot