question

venkatreddy avatar image
venkatreddy asked

How to copy logins/permissions from one server to anathor server?

How to copy logins/permissions from one server to anathor server? What are the best practices to approach? Generating the scripts in one database and deploying them over anathor server will copies the logins and permissions? Thank you all.

sql-server-2005securitypermissionslogin
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Queue avatar image
Queue answered

I suppose you would like to go through this microsoft kb article and use the script they have given there.

http://support.microsoft.com/kb/246133

And if it is the same database, say you are trying to do a backup restore of a database from one server to another then the above will work for permissions as well.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

venkatreddy avatar image venkatreddy commented ·
@Queue,am using different server
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered

This was discussed here: http://ask.sqlservercentral.com/questions/6115/when-should-i-transfrer-the-logins-and-passwords/6118#6118.

The important concept to understand is that everyone who accesses any database on a server does so through a login, which is at the server level and is stored in a system database. A login gets access to an individual database by being mapped onto a user, which is at the database level and is stored in that database. The database user has the ID of the server login. If you copy a login and database to another server you either have to ensure that you give the login the same ID - which the article Queue references will do - or you have to use sp_change_users_login to correct the login ID on the database user.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OnoMrBill avatar image
OnoMrBill answered
Just a reminder that sp_change_users_login should no longer be used, as it is deprecated by Microsoft, and that ALTER USER should be used instead. If copying users/Logins within the same Domain, life is simpler; however, if restoring databases from a PRODuction Server to say, a TEST Server in a separate Domain, then Windows users will have to be replaced (e.g. PROD\\userA doesn't exist in the TEST Domain: TEST\\userA.) Our developers, for example, have this Situation and have created a BackupRestore process that uses a BackupRestore database containing a **BackupRestore_Config** table and a **BackupRestore_User** table that saves the target db users, restores the PROD db to the TEST db Server, then Drops the invalid PROD db users and re-writes the saved target db users back to their respective databases and re-links the users to the Logins.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.