RE: Off topic MS SQL Server

From: Powell, Mark <mark.powell2_at_hpe.com>
Date: Wed, 6 Jan 2016 20:11:51 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD1306A4272_at_G9W0741.americas.hpqcorp.net>

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<mailto: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<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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 06 2016 - 21:11:51 CET