Saturday, September 30, 2017

How to Fix Orphaned SQL Server Users

No, not this kind of Orphan -- too
bad we can't fix this issues as easily!
Just a quick entry this time.  However, it happens all the time: you restore a database to a new server and can't login to the database with the original usernames.  It happens because the usernames have become "Orphaned Users."

It's now easy to fix, as of SQL 2000 sp3.  It used to be REALLY hard before.

First find all your orphaned users, by running this command:

EXEC sp_change_users_login 'Report'

It will yield something like this:

jobean 0x2BB8F8255C002E47B232FB331B1698BA

From this, I know 'jobean' is an orphaned user.

So, run this command to fix it:

EXEC sp_change_users_login 'Auto_Fix', 'jobean'

The row for user 'jobean' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

This hooks up the jobean user of the database to the jobean of the database server.  Did this fail (probably because you don't have a jobean user in the database server?)  Then issue this command to make a new user and wire it up to the jobean orphaned user.

EXEC sp_change_users_login 'Auto_Fix', 'jobean', 'login', 'password'

[Where login and password are those you want assigned to the new user]

Easy as can be.

No comments:

Post a Comment