Transfer login task using SQL 2005 . I already tried SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Transfer login task using SQL 2005 . I...

11 posts, Page 1 of 2

1 2 »»

Transfer login task using SQL 2005 . I already tried SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

Dean Jones-454305

Posted Wednesday, August 12, 2009 3:34 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 15, 2013 2:29 AM
Points: 282, Visits: 1,047

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.

Post #769179

luckysql.kinda

Posted Wednesday, August 12, 2009 4:56 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:19 AM
Points: 309, Visits: 645

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

Post #769202

MANU-J.

Posted Wednesday, August 12, 2009 3:56 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 1,654, Visits: 8,563

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

Post #769750

Sarab_SQLGeek

Posted Saturday, August 15, 2009 5:42 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:05 AM
Points: 368, Visits: 520

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 Cool

SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek

Post #771446

Dean Jones-454305

Posted Saturday, August 15, 2009 6:27 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 15, 2013 2:29 AM
Points: 282, Visits: 1,047

I can see this, but this does not resolve permission problems though.

Post #771449

Sarab_SQLGeek

Posted Saturday, August 15, 2009 6:42 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:05 AM
Points: 368, Visits: 520

What sort of permission problems you are facing, pls. share so we can help you.

Regards,
Sarabpreet Singh Cool

SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek

Post #771451

Dean Jones-454305

Posted Saturday, August 15, 2009 6:52 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 15, 2013 2:29 AM
Points: 282, Visits: 1,047

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.

Post #771453

Sarab_SQLGeek

Posted Saturday, August 15, 2009 7:13 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:05 AM
Points: 368, Visits: 520

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 Cool

SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek

Post #771455

Dean Jones-454305

Posted Saturday, August 15, 2009 7:20 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 15, 2013 2:29 AM
Points: 282, Visits: 1,047

Ok

I understand it now, so what does the SSIS task do because from what i have seen, it doesnt work properly.

Post #771456

Sarab_SQLGeek

Posted Saturday, August 15, 2009 7:32 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:05 AM
Points: 368, Visits: 520

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 Cool

SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek

Post #771458

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions