Fix Orphan Users for All databases

Question

Fix Orphan Users for All databases

premkumardr

Say Hey Kid

Points: 708
More actions
March 20, 2017 at 6:09 pm

#319801

Comments posted to this topic are about the item Fix Orphan Users for All databases

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

i think that resetting the database user(s) to match the login is the backwards way to do it; you have to re-do it with every restore.

i prefer to script out the user from , say production via sp_help_revlogin or something similar, so you have the sid, and drop and recreate the sql login on dev, then change the password on dev.

then the sids ALWAYS match, forever, and tno subsequent restores form production suffer from orphans any more.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Therese Hutcheson SSC Eights! Points: 823 More actions · Answer 2

We have a test server where we restore a database and adjust the name adding the date to identify the version. Each time we do this we need to set the User from the database to the Server login. This seemed like a good solution for us. However running the code is giving me an error:
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near '..sysusers su
JOIN sys.server_principals sp ON sp.name = su.name
WHERE issqluser = 1 AND
(su.sid IS NOT NULL AND su.sid <> 0x0'.

The code in question starts with: FROM '+quotename(@DatabaseName)+'..sysusers su

I am unsure where to go from here. Suggestions?

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 3

I blogged an alternative method of unorphaning SQL Server users a few years ago, when I was having to do this process every month or so. Fun times.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Therese Hutcheson SSC Eights! Points: 823 More actions · Answer 4

Thank you for the information. I like the code and am going to test, however when I run the EXEC sp_change_users_login 'Report' on a database with several users, connected to a login or not, it doesn't return any records. I ran the code on master and several different databases (SQL 2014 and SQL 2016) all with Users. This is so simple I can't imagine what I am missing but obviously I am .

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 5

Therese Hutcheson - Wednesday, April 19, 2017 11:41 AM
Thank you for the information. I like the code and am going to test, however when I run the EXEC sp_change_users_login 'Report' on a database with several users, connected to a login or not, it doesn't return any records. I ran the code on master and several different databases (SQL 2014 and SQL 2016) all with Users. This is so simple I can't imagine what I am missing but obviously I am .

sp_change_users_login 'Report' will only report on orphaned users - one where the SID in the database's sys.users table doesn't match the SID in the server's master.sys.logins table. (This is from memory - might not have the table names quite right here...) See https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-change-users-login-transact-sql for more.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Therese Hutcheson SSC Eights! Points: 823 More actions · Answer 6

Therese Hutcheson

SSC Eights!

Points: 823

April 19, 2017 at 2:06 pm

#1938949

I really appreciate your help.