Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Moving Your Users with Their Databases Expand / Collapse
Author
Message
Posted Thursday, October 13, 2005 1:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 2:21 PM
Points: 55, Visits: 168
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Post #228793
Posted Wednesday, November 16, 2005 10:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 4:07 AM
Points: 75, Visits: 394

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.

 




Post #237577
Posted Thursday, June 8, 2006 7:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 27, 2010 8:38 AM
Points: 236, Visits: 99

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.




Post #285917
Posted Thursday, June 8, 2006 11:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 4:07 AM
Points: 75, Visits: 394

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!




Post #286045
Posted Monday, June 12, 2006 6:38 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 2:45 PM
Points: 6,794, Visits: 1,906
Rob, you give individual users sql logins?

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #286616
Posted Tuesday, August 15, 2006 8:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 20, 2009 9:55 AM
Points: 1, Visits: 4

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.

 

Post #301854
Posted Thursday, November 16, 2006 7:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 12, 2014 8:54 AM
Points: 195, Visits: 80

Very nice article!

Addresses a key problem area in SQL Server!

Post #323421
Posted Thursday, November 16, 2006 9:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 31, 2007 8:22 AM
Points: 92, Visits: 1

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

Post #323515
Posted Thursday, November 16, 2006 10:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:38 PM
Points: 216, Visits: 425
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
Post #323543
Posted Friday, December 15, 2006 8:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 10, 2008 6:17 PM
Points: 48, Visits: 2

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

Post #330844
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse