The link below article does not include the ‘update_one’ option which will
match the database username to the instance name making the database user SID
match the instance SID (Internal ID) in the situation where the usernames
already exist in both as would be the likely case if the database had been
previously restored from the same source like prod to test.
Beware of posted scripts to generate the sp_change_users ‘update_one’
statements to fix the orphaned users since most scripts assume that the
database username matches the instance username of the same name. It is in
fact possible for the mapping to be between different usernames and the correct
match up can be queried but most of the scripts use just one query to get the
name value used for both the database and instance username.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Kellyn Pot'Vin-Gorman
Sent: Wednesday, January 06, 2016 10:16 AM
To: rjoralist3@xxxxxxxxxxxxxxxxxxxxx
Cc: ORACLE-L
Subject: Re: Off topic MS SQL Server
Although BCP is the correct answer, you can also correct the orphaned logins
to users with sp_change_user_login. I now work for Oracle and rarely do MSSQL,
but here's your answer,
http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm
Good luck,
Kellyn
On Jan 6, 2016 07:59, "Rich J"
<rjoralist3@xxxxxxxxxxxxxxxxxxxxx<mailto:rjoralist3@xxxxxxxxxxxxxxxxxxxxx>>
wrote:
On 2016/01/06 06:55, angelo wrote:
Good morning John,
Try to make a backup/ restore of the database, all of whole schema from the
database you had targeted for backup will be included.
The backup/restore does work, but it breaks security if server logins are
mapped to database schemas, which they likely are (aka "orphaned users"). And
I'm excluding things like replication and encryption that I thankfully have not
had to deal with in the SS world.
Since I've had to deal with SQueaL Server, I've had good luck finding many
answers at places like sqlservercentral.com<http://sqlservercentral.com>.
Googling for SS answers has often been counterproductive for me due to the
morass of "expert" blogs.
Or try PostgreSQL instead. ;)
GL!
Rich
