Re: transfering logins

I'd have to agree with Erland on this one. If you are planning on making a
habit of this, don't.

Now, what makes a user orphaned to begin with, and thusly, what does
sp_change_users_login do to fix it? This is actually a great question, and
one we use on DBA interviews all the time. If you don't know the answer, do
not even think about applying for a position at our company. (Just
kidding...sort of.)

If you look at master.dbo.sysxlogins, you will notice that each system login
has their name, password, and a SID, among other attributes.

In <your database>.dbo.sysusers, you will notice that each database user has
their name and SID, among other attributes.

If you compare the two, you will notice that the SIDs match, and thus you
have found the glue that ties the two together. For an orphaned database
user, the sysusers table comes over with the migrated database (it is a
database not a server object), but since the SIDs are generated at random,
the probability that two system logins, and thus two database users, would
have the same generated SID values is pretty low.

However, even if the names were different, suppose you restored a database
that had a user with a SID value the same as an existing system login? Yep,
they would match, and the user would not be orphaned, but the user name
would not match the system name, which could through you off a bit. It does
give you insight on 1) how to permanently fix your orphaned users problem,
2) why sp_help_revlogin fixes the problem, and more importantly 3) why you
do not have this problem with Windows logins (hint: Active Directory
generates SIDs too).

Now, for password synchronization, sp_help_revlogin shows you what to do as
well: you can change a password using the encrypted form, but you will have
to make updates to system tables (which can be dangerous if you don't know
what you are doing).

I would recommend that you offline your databases, drop the logins, and then
readd the logins with the sp_help_revlogin output, and bring the databases
back online.

Sincerely,


Anthony Thomas




--

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns98D0315C56BBYazorman@xxxxxxxxxxxx
ToNewToKnowBetter (ToNewToKnowBetter@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I made a backup of database A on Server A and have restored database A
to database B on Server B. In doing so, I am aware of all the issues
with the orphaned users and have corrected this problem using
sp_change_users_login. I Since we use JD Edwards, I have several (always
too many) database users and server logins that I need to copy over from
server A to server B. I have reviewed the process that creates the
sp_help_revlogins which dumps out the users and encrypted passwords and
I would love to use this to add them on to Server B. The problem is
that Server B already has these logins in place. I am more interested
in the passwords and getting them in sync.......whatever password used
for users on Server A I would like to have for Server B. Does this make
sense? Maybe by default the sp_change_users_login fixes this???

No, sp_change_users_login oes not change that.

The brutal way would be to drop the logins, and recreate them. But then
you would of course also get orphaned users in the existing databases
on server B.

But I have to say that this password replication sounds a little iffy
to me. Is the idea that if a user changes his password on server A,
it will be replicate over to server B without his intervention?

Wouldn't life be easier if you used Windows logins?


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.

Relevant Pages

  • RE: Creating a DB User *not* on current Machine
    ... detach/attach the database to a new server. ... Server.When you move a database from one server that is running SQL Server ... the security identification numbers of the logins in the master ...
    (microsoft.public.sqlserver.security)
  • Re: fix logins
    ... it is used exactly for SQL Server logins. ... See Books Online, sp_addlogin, and you'll find a parameter for specifying the SID. ... password stored inside a database, ...
    (microsoft.public.sqlserver.server)
  • Re: Logins
    ... no databases are checked, ... On the Server Roles tab, ... That server was upgraded to SQL Server ... moved, I recreated the SQL Server logins we had added, making sure that the ...
    (microsoft.public.sqlserver.security)
  • Re: NT > 2003 domain name changes
    ... Can't use the DTS option as it doesn't retain the SID, ... -restore database to SQL2K ... - cut out logins that do not apply to the database at hand ... >> windows authentication over to a new sql server on a new ...
    (microsoft.public.sqlserver.security)
  • RE: Environment variables
    ... my $sid; ... Once connected normal database security takes over. ... servers, I probably have to log in to each server before connecting. ... > my $dbh; ...
    (perl.beginners)
Loading