Need help transferring SQL logins/passwords to other server

[alistairpaul]

Here's my issue - I'm fairly new to SQL Server and need to make a copy of a database on one server and move it to another server. I need the users/logins in this database to follow over to the other server, and I need the permissions to be applied correctly.

My pitfall at the moment seems to be getting the logins scripted out of the master db on the original server, and getting them into the new server. I can't seem to get the passwords to come over correctly!

Here's what I'm doing:

select 'sp_addlogin @loginame = ' + name +
', @passwd = " + CONVERT(VARBINARY(32), password) +
'", @encryptopt = skip_encryption' +
char(13) + 'go'
from master..syslogins
where name in (
select master..syslogins.name
from master..syslogins inner join sysusers
on master..syslogins.suid = sysusers.suid
where master..syslogins.sysadmin=0
)

- The problem is that it won't allow me to add regular text to the converted password. If I split this all up and get the password using the same convert formula, then paste that into the actual sp_addlogin text created for me, it works like a charm. I can't seem to get it to put that big long hexadecimal-looking string into my text even if I convert/cast it again.

Any ideas?!

Thanks!

 

[shepherd]

DTS has tools to do this. Why don't you just choose Export from the Action menu to export the database to the new server? It should automatically bring you through the DTS wizard which allows you to choose options for export, including exporting user roles, permissions, etc.