April 5, 2006 at 3:35 pm
I have a question concerning orphaned users, I frequently am required to refresh test databases with production data (located on different servers). Using a production backup to restore the data in test works very well. However, as anyone who has done this knows you are left with orhpaned users. This problem is easily corrected however I recently encountered a situation which I have not found a way around. I found a script that will display orphaned users and I use either sp_revoklogin or sp_revokedbaccess to remove the user. However, this will not work if the orphaned user is DBO. The error that comes up says can't remove database owner. If I attempt to create a new logon with the user having the DBO role in the database SQLServer says that user already exists, but you can't see the user. Is there a way to get around this and delete a user with DBO role? Thanks!
Ken
April 5, 2006 at 5:34 pm
The best way to deal with orhpaned user is to create the user with same SID in test environment. This will resolve the problem for good. Let us know if you need the scripts to create user with same SID. Try for yourself to write one as this will help you in understanding the bigger picture also
Amit Lohia
April 6, 2006 at 5:57 am
sp_change_users_login
April 10, 2006 at 9:41 am
Thank you both for your input!
April 10, 2006 at 11:11 am
There is a free GUI tool out there called Sync SQL Logins which could be used to synchronize the logins
Mike
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply