Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Your Users with Their Databases


Moving Your Users with Their Databases

Author
Message
scb
scb
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 175
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
kpsqlcent
kpsqlcent
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 512

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
firthr
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
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.





kpsqlcent
kpsqlcent
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 512

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
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: Moderators
Points: 8112 Visits: 2711
Rob, you give individual users sql logins?

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Shailesh-336918
Shailesh-336918
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.


Jambu Krishnamurthy
Jambu Krishnamurthy
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 98

Very nice article!

Addresses a key problem area in SQL Server!


Carlos Urbina
Carlos Urbina
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 1

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


Rick Todd
Rick Todd
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 441
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.
David S.
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search