Re: Off topic MS SQL Server
Date: Wed, 6 Jan 2016 13:17:48 -0700
Message-ID: <CAN6wuX3X9ysdHtmbGWGyfTx28dJg3gL3YVo6xxNjSqz6ShuQfA_at_mail.gmail.com>
Hi Mark,
Yes, you can map any login to any username, so they don't have to match. I had scripts that had known users and thank you for remembering the update_one option. Its been a long time since I was in MSSQL and the skills have gotten quite rusty....
Kellyn
[image: Kellyn Pot'Vin on about.me]
Kellyn Pot'Vin-Gorman
about.me/dbakevlar
<http://about.me/dbakevlar>
On Wed, Jan 6, 2016 at 1:11 PM, Powell, Mark <mark.powell2_at_hpe.com> wrote:
> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Kellyn Pot'Vin-Gorman
> *Sent:* Wednesday, January 06, 2016 10:16 AM
> *To:* rjoralist3_at_society.servebeer.com
> *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_at_society.servebeer.com> 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. Googling for SS answers has
> often been counterproductive for me due to the morass of "expert" blogs.
>
>
>
> Or try PostgreSQL instead. ;)
>
>
>
> GL!
>
> Rich
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 06 2016 - 21:17:48 CET