August 12, 2009 at 3:34 am
Hi Guys,
I am currently trying to setup a transfer login task for SSIS, at the moment I notice that if I select transfer all logins, it doesn’t seem to work but if I choose selected logins, it tends to work.
I am wondering if there is a more efficient way to transfer login from one server to the other and also making the process automated. This was the reason why I wanted to use SSIS.
I have seen this Microsoft site which provides a script to do this, but my concern is that the result of the stored procedure is what needs to be executed on the other server. See the site on http://support.microsoft.com/kb/918992/
Thanks.
August 12, 2009 at 4:56 am
I read somewhere that you can bcp all logins to hard drive and then import them back using bcp on other server. Unfortunately I don't have readymade code for you. I shall try to search. Can anyone else post that code?
-LK
August 12, 2009 at 3:56 pm
Syntax: bcp master..syslogins out -U -P -S -c
Example: bcp master..syslogins out C:\logins.txt -Usa -P -SServername -c
On the target server, BCP syslogins in
Syntax: bcp master..syslogins in -U -P -S -c
Example: bcp master..syslogins in C:\logins.txt -Usa -P -SServername -c
HTH!
MJ
August 15, 2009 at 5:42 am
You can use the SP sp_help_revlogin
which is given by Microsoft. http://support.microsoft.com/kb/246133
You will find 2 SPs and you need to create both of them in your master db.
Then execute this SP (SP_HELP_REVLOGIN) in your current servers Master DB without any parameter and copy the result, then execute the result on your New Server this is a easiest as well as BEST way to transfer logins from one server to another server.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 15, 2009 at 6:27 am
I can see this, but this does not resolve permission problems though.
August 15, 2009 at 6:42 am
What sort of permission problems you are facing, pls. share so we can help you.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 15, 2009 at 6:52 am
What i mean is that the code will create the login in the master DB, but it will not map permissions to the user databases.
i.e grant permissions for user DB's etc.
August 15, 2009 at 7:13 am
Dean, i am not getting you.
What do you mean by
code will create the login in the master DB, but it will not map permissions to the user databases.
In SQL Server logins are always created in master db.
You are talking about the user DB permissions, that will be fixed when you backup the user db and restore on that server. otherwise you need to give permissions again.
This forum post was talking about logins not users.
Do this way
first backup the user db and restore it on new server
then run that sp and execute the result in new server
that should solve the issue, if the issue persists then you can use sp_change_user_login with auto_fix.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 15, 2009 at 7:20 am
Ok
I understand it now, so what does the SSIS task do because from what i have seen, it doesnt work properly.
August 15, 2009 at 7:32 am
May be.
I haven't tried it. I use SSIS but to transfer logins this sp seems gud to me and moreover it has been tried and tested on many instances of SQL 2000 so never thought of using this one.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
February 4, 2010 at 6:26 am
Hi i am trying to do the same.
But i have som other problems : )
I can get the SSIS to copy all logins to the other server.
But i can't get the users enabled and sync the password.
The problem you are having with no logins transferd is probably because all users hade master as default database.
Best regards
Johan
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply