Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script for Copying Logins from one server to other server with database


Script for Copying Logins from one server to other server with database

Author
Message
Anil Kumar Kurra
Anil Kumar Kurra
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 16
Hi,

I want to copy a set of windows Logins & Database Logins from one server to other sever with same roles setup & Passwords.

Please guide me how to do this.

Cheers,
Anil
Nicholas Cain
Nicholas Cain
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 6200
Take a look at http://support.microsoft.com/kb/918992. This works in 2008 as well as 2005.



Shamless self promotion - read my blog http://sirsql.net
Greg Charles
Greg Charles
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4089 Visits: 5823
I've used this script, which is a modified version of sp_help_revlogin, to script out logins, server roles, database users, database roles, and object permissions. Remember that the databases need to exist in the destination server before you can create users. Considering that, you might as well backup and restore the databases, which would also move the database users and their permissions.

http://www.sqlservercentral.com/scripts/31776/

Greg
Anil Kumar Kurra
Anil Kumar Kurra
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 16
Hi All,

Thanks for the reply. If we Back up & restore the database in another server whether all security Objects like Users,
Roles etc will also move to new server.

Suppose if some of the users alreday exists on new server is there any way to skip that users.

Please let me know on this.

Cheers,
Anil
Anil Kumar Kurra
Anil Kumar Kurra
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 16
Hi All,

Sorry, One more question if the User IDs already exists in the server I need replace the User IDs during Copy of logins.

Please let me know is this possible?

Cheers,
Anil
Greg Charles
Greg Charles
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4089 Visits: 5823
Anil Kumar Kurra (4/23/2009)
Hi All,

Thanks for the reply. If we Back up & restore the database in another server whether all security Objects like Users,
Roles etc will also move to new server.

Suppose if some of the users alreday exists on new server is there any way to skip that users.

Please let me know on this.


The difference between a login and an user - a login allows a person to connect to SQL Server; a user allows a person to access objects in a database.

So users only exist in databases. If you restore a database users, roles, and schemas are also restored. After copying logins and databases to a new server, you may need to re-sync the users with their logins. See sp_change_users_login in BooksOnLine.

Greg
nhashmi
nhashmi
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 260
Hi SSCarpal Tunnel,

And thanks for providing excellent script and saving be lot of headaches.

Cheers.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search