Exporting Users 3

[tiver43809]

I am not sure I should be posting this here or in setup and admin. We have a program that uses sql authentication. If we add a new user we can have sql add it in sql under security and logins. Here is the problem, if we backup and then restore to a different sql server, all our users are there, but I have to go into sql and add all the users. Is there a way to export the logins and properties out of sql and then reimport them back in at a later date?

Thank You,
Terry who is NOT a DBA.

 

[jrbarnett]

http://support.microsoft.com/kb/918992

John

 

[tiver43809]

This was just what I was looking for. I thought I searched the KB really well. Wonder if I had a type in my search, this is perfect

 

[jrbarnett]

To expand, this is the equivalent article for SQL Server 7 and 2000 (and also for moving logins from those versions to SQL 2005):

http://support.microsoft.com/kb/246133

Basically it happens because users are defined at the instance level, with the basic details stored in the master database (user id, encrypted password etc).
However, the permissions for specific users in a specific database are defined within that database. The link between the two are defined by the user's SID. When you do a database backup and restore to a different server, you get the data transferred, but the SIDs and the details to which they refer still remain in the master database.
You can't transfer the whole master database, since this stores the entire SQL Server configuration.

John