Preserving logins and permissions

[cjkenworthy]

I'm due to upgrade an SQL 7.0 server to 2000 soon - and will be wiping it clean and restoring the data from a backup.

My plan is to use SQL's own backup to backup the databases to file - then restore the data once the fresh installation is complete.

My concern is however that, although backing up to file does preserve login information for each database - how do I preserve the Logins detailed for the SQL srever (listed under 'Security' in Enterprise Manager).

Is there any way to backup these logins and permissions? Will the restore fail on my new server due ot the absence of the logins under 'Security' (they are domain and standard users for DSNs and connection strings)

Thanks.

 

[nigelrivett]

The restore will succeed.
Look at master..syslogine and mydb..sysusers.
The sid in syslogins maps to sysusers to give the login permissions in the database. If these are incorrect due to a restore the permissions will be messed up.

There is an sp sp_change_users_login which will attempt to fix the links.

You could restore master but it's probably better to add the logins via a script. You can generate the script via a query on syslogins.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[cjkenworthy]

That sounds interesting - I guessed it would be a bad idea to restore the Master database.

Could you give more information on generating a script to add the logins?


(The domain accounts will still be there after the domain upgrade, I presume this maps to the SIDs?)