x

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.

more ▼

asked May 06, 2010 at 02:17 AM in Default

venkatreddy gravatar image

venkatreddy
562 28 29 32

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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.

more ▼

answered May 06, 2010 at 04:27 AM

David Wimbush gravatar image

David Wimbush
4.9k 28 30 34

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 06, 2010 at 03:11 AM

Queue gravatar image

Queue
250 2 2 3

@Queue,am using different server
May 06, 2010 at 03:21 AM venkatreddy
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x162
x87
x84

asked: May 06, 2010 at 02:17 AM

Seen: 6168 times

Last Updated: May 06, 2010 at 02:17 AM