Moving Your Users with Their Databases

  • scb

    SSCommitted

    Points: 1859

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp

  • kpsqlcent

    Old Hand

    Points: 385

    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.

     

  • firthr

    SSCommitted

    Points: 1606

    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.

  • kpsqlcent

    Old Hand

    Points: 385

    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!

  • Andy Warren

    SSC Guru

    Points: 119675

    Rob, you give individual users sql logins?

  • Shailesh-336918

    Grasshopper

    Points: 21

    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.

     

  • Jambu Krishnamurthy

    SSCrazy

    Points: 2054

    Very nice article!

    Addresses a key problem area in SQL Server!

  • Carlos Urbina

    SSCommitted

    Points: 1790

    Great article! Will put some of the suggestions into place today.

  • Rick Todd

    Hall of Fame

    Points: 3302

    Thanks for the article (even when it's reposted!). I'm going to try using this technique for some problems we're having with mirroring in SQL2k5, with slq authentication users, where they don't come over properly when you setup the database on the mirror server. If it works I'll post it.

    Cheers,

    Rick


    Rick Todd

  • David S.

    SSC Veteran

    Points: 218

    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.


    The truth is just a point of view accepted by majority

    David Stohlmann

  • david 69093

    Grasshopper

    Points: 11

    This doesn't work for moving logins from 2005 to 2005/2008. Check this link out for that:

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

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply