July 27, 2008 at 6:24 pm
Hi Folks,
I believe there is a way to transfer db users and logins in sql server using Business Intelligence or SSIS. Does anyone know of any articles to outline this (any help appreciated)
July 27, 2008 at 11:03 pm
bodhilove (7/27/2008)
Hi Folks,I believe there is a way to transfer db users and logins in sql server using Business Intelligence or SSIS. Does anyone know of any articles to outline this (any help appreciated)
I think, once you restore a database from one server to the other, all the logins would be transferred, you would need to map the orphaned users.
July 28, 2008 at 1:12 pm
Logins don't move with a database so you'd either have to create them or transfer them seperately.
http://support.microsoft.com/kb/918992/en-us
There is an SSIS task called the Transfer Logins Task. BOL has instructions for it.
Greg
July 28, 2008 at 1:21 pm
sp_change_users_login is a way to sync logins to users.
If you are moving databases, typically you move the logins once, then each time you restore, you might have to re-sync things.
July 28, 2008 at 11:05 pm
Steve Jones - Editor (7/28/2008)
sp_change_users_login is a way to sync logins to users.If you are moving databases, typically you move the logins once, then each time you restore, you might have to re-sync things.
Yes that right! ..once the restore is done, we need the following commands to be executed :
This will lists the orphaned users:
EXEC sp_change_users_login 'Report'
If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'
If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'
thanks,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply