Urgent Moving a SQL 2000 db to a new server

Question

Urgent Moving a SQL 2000 db to a new server

houston

Ten Centuries

Points: 1257
More actions
May 13, 2005 at 9:56 am

#167063

I did a backup of a database on server a moved it to server b, before restoring I created two SQL id's that I need. Restored the database and attempted to assign permissions for those two id's (one is the table owner for the db). I get a response that the user or role already exists on the db....is there a way to do this?

Viewing 10 posts - 1 through 9 (of 9 total)

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

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 1

Check out sp_change_users_login in Books Online. If your server login name is the same as the database user, you should be able to get their IDs to match up using sp_change_users_login.

veer SSC Enthusiast Points: 177 More actions · Answer 2

Just resolve the Orphan Users and it would work fine soon

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 3

sp_change_users_login basically handles the oprhaned users (depending on what options you use). There is a script on this site that loops through all users in the database and runs sp_change_users_login for each of them (with te auto_fix option). I've used that script a lot when automating a backup/restore to a reporting instance.

houston Ten Centuries Points: 1257 More actions · Answer 4

Thanks...I was able to resolve one id but the one that is the the table owner and is a dbo for the database I can't change. I tried to alias the id but it won't let me because it already exists? I can't drop the id so, I'm not sure what else to try to resolve the orphans?

houston Ten Centuries Points: 1257 More actions · Answer 5

I tried it the Auto_Fix value and it would'nt work. the error was: The Action 'Auto_Fix' is incompatable with the other parameter values ('(NULL)', '(NULL)').

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 6

auto_fix (From BOL):

Links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins. You should check the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.

When using Auto_Fix, you must specify user and password; login must be NULL. user must be a valid user in the current database.

If the Login in named the same/similar to the db user, the auto_fix should map the user to the correct login. Make sure to double check, though.

houston Ten Centuries Points: 1257 More actions · Answer 7

Is this what you mean

sp_change_users_logins 'Auto_fix'

,@UserNamePattern = 'user'

,@LoginName = 'null'

,, @passwd = "'password'

houston Ten Centuries Points: 1257 More actions · Answer 8

Thanks for all your help...I finally got it andit appears to have worked as you suggested.

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 9

No problem. Look for that script in the script section (I think it is called something like fix orphaned users) it makes the process a no brainer. Here's a copy:

declare @usrname varchar(100), @command varchar(100)

declare Crs insensitive cursor for

select name as UserName from sysusers

where issqluser = 1 and (sid is not null and sid <> 0x0)

and suser_sname(sid) is null

order by name

for read only

open Crs

fetch next from Crs into @usrname

while @@fetch_status=0

begin

select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '

exec(@command)

fetch next from Crs into @usrname

end

close Crs

deallocate Crs