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.
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.
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.
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.
1 Person is following this question.