November 6, 2008 at 12:41 pm
I have to migrate all the 10 databases with users having different privileges from one server another server.
1. I did test the migration by restoring the databases from backup, but I could not get all the privileges for the users and that I had to create the logins again, next
2. I did copy the data and log files to the new server and did attach the databases, still I could not get all the privileges and I had to recreate it.
Am I missing some thing in doing this effectively.
November 6, 2008 at 2:37 pm
Logins on different servers will always have different SIDs so when a database that originated on another server is restored or attached, it's likely that the users in the database won't match the logins.
You can sync the users with the logins on the new server by running sp_change_users_login. See the entry for it in BooksOnLine for syntax. I recommend running it with the 'Report' argument first to see which users need to be remapped to a login.
Greg
November 6, 2008 at 2:45 pm
Once the logins are moved, if you repeat the process, they will remain there (assuming you're not messing with the master database). You will still need to use sp_change_users_login to match things up again between different servers.
November 6, 2008 at 3:10 pm
And ... Server-level privileges have to be migrated as well
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply