January 23, 2008 at 11:39 am
Hi,
I have a SQL 2000 + IIS environment that i'm attempting to replicate. I took a copy of the databases and put it on my test machine, as well as making sure the IIS environments are identical. Now i'm having trouble getting the orphaned users to come back. The only thing I can think of is that some of these are local machine users, so the machine name is different.
Can someone give me some help on how to unorphan these? Thanks!
January 23, 2008 at 11:59 am
Well, ensure your test machine has the matching logins ... once that is completed, you can run this to sync up your orphaned users to the corresponding login:
sp_msforeachdb
'USE ?
DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ''update_one'', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END CLOSE fixusers
DEALLOCATE fixusers
'
January 23, 2008 at 1:07 pm
that worked to a point...there are 2 users which i can't get to not be orphaned...
one of em is a regular user who is a sys admin and its spelled the same and everything..
the otehr is the "servcie account which on the old server is enterprise5\entsrvacct and on the new is enterprise6\entsrvacct
Thanks!
January 23, 2008 at 1:11 pm
Matt Rose (1/23/2008)
that worked to a point...there are 2 users which i can't get to not be orphaned...one of em is a regular user who is a sys admin and its spelled the same and everything..
the otehr is the "servcie account which on the old server is enterprise5\entsrvacct and on the new is enterprise6\entsrvacct
Thanks!
A sysadmin login does not need to have a database user; thus simply recreating the login with sa privileges will suffice and you can remove the old user from the database.
As for the service account user ... you'll have to drop the user and recreate the new one. The point of fixing an orphan user is to match the same named database user to the same named login. Obviously this does not apply to this particular user.
Does that make sense?
January 23, 2008 at 2:28 pm
yes, looks great, but now i'm getting web.config errors from the application....dang lol
any idea of a forum for that?
January 23, 2008 at 2:37 pm
Matt Rose (1/23/2008)
yes, looks great, but now i'm getting web.config errors from the application....dang lolany idea of a forum for that?
February 16, 2008 at 6:19 am
Great Script Adam, saved me alot of time!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply