Great article! We do something similar and also take account of server roles by querying syslogins and generating the syntax for sp_addsrvrolemember. Probably not an issue for most people.
How do you transer password when the login pre-exists though?
You would have to script out any exisiting roles, then drop these, then drop the login then add the logins and password thoguht the revlogon script, reapply the roles then fix up any orphaned users??!
We have our users change every 30 days so they could not remeber their inital password from ages ago..
Any thoughs appreciated..
Rob.
Yes the limitations are as you describe (we only do migrations of logins when transferring applications to a new instance) so I haven't looked at just transferring passwords.
I'd check whether Microsoft have documented a solution for log shipping. There has to be some method of keeping logins in step.
The only other way I can think of (SQL2000) is moving the contents of column password (master..sysxlogins) from one server to another. I *guess* this would work but you'd have to be VERY careful as it will lead to big trouble if it goes wrong. Sounds too risky. I had a quick look at sysxlogins on one of our SQLs and saw multiple entries for one login (linked servers) and that's just a complication I spotted. Goodness knows whether (or how) you need to take account of differences in collations or software levels between servers. I'm not sure I've been much help - sorry!
Thansk Corey !
Great article ! Tried Database and User move - first time, I'm not a DBA, but was asked to do so- based on this article.
DB and User login move worked perfect. Only other move I did was few jobs which I generated script and ran on Destination server.
nice helpfull and to the point article.
thanks again.
Very nice article!
Addresses a key problem area in SQL Server!
Great article! Will put some of the suggestions into place today.
This is a great script, but the version used for SQL Server 2000 doesn't work with SQL Server 2005 because the sysxlogins table doesn't exist.
Microsoft published a new version of this script that is compatible with SQL Server 2005 and it's documented in KB 918992
I tried this version on one of our SQL Server 2005 servers and it works just as the original version.
