May 13, 2005 at 9:56 am
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?
May 13, 2005 at 10:18 am
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.
May 13, 2005 at 10:56 am
Just resolve the Orphan Users and it would work fine soon
May 13, 2005 at 10:59 am
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.
May 13, 2005 at 11:19 am
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?
May 13, 2005 at 11:28 am
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)').
May 13, 2005 at 11:31 am
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.
May 13, 2005 at 12:02 pm
Is this what you mean
sp_change_users_logins 'Auto_fix'
,@UserNamePattern = 'user'
,@LoginName = 'null'
,, @passwd = "'password'
May 13, 2005 at 12:55 pm
Thanks for all your help...I finally got it andit appears to have worked as you suggested.
May 13, 2005 at 1:03 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply