sql serversql-server-2008-r2
I am trying to migrate my database from SQL Server 2005 to 2008 R2 test, I did follow back up and restore method to migrate the database, and I ran the QUERY provided by Microsoft to transfer logins and password.
But is there a way I should see if all the login and password are moved correctly?
Is there any documentation to follow during migration? I tried looking online couldn't find one.
Thanks
OK, there are several things that it could be but I'm not sure if we can determine which from here. These include the following:
And yes, dropping and re-adding the Users does seem to fix it in some of these cases, even though this should in theory never be needed because the SIDs should always match for Windows Users in transferred databases. It may be that the target SQL server simply lacks the same necessary server-level principal definitions as the original servers (and this as a side-effect creates explicit ones), or it may be that the act of adding the Login/user causes SQL Server to forcibly rerun the query and reload it's AD information for that user.
In any event, the real problem tends to be that dropping a user is a potentially disruptive and destructive act. It may work, but it may also cause some bad side-effects. Nonetheless, if you want to try that, then the best procs I know of for that are from Ted Krueger and they are located here: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users.
Other (possible) solutions that you could employ include:
For logins, this is better IMHO:
http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror
And for jobs, you don't need SSIS. Just highlight the Jobs node in Management Studio, click on Object Explorer Details (F7), in the right pane select all, right-click and script.
I'd do the logins first, in case any of the jobs involve any of the logins.
Best Answer
Use something like SSDT to do a schema comparison on the before and after. Check this out: https://msdn.microsoft.com/en-us/library/hh272690(v=vs.103).aspx
Note: This is only for the logins, obviously showing passwords isn't supported