As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer. Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic. Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.
Security question
The next question in the list is:
How can you migrate users and passwords from one server to another server?
This is an easy one. So easy, in fact, that rather than try to come up some clever code to show how you can do the migration yourself, I’ll show you where it is documented by Microsoft. Microsoft offers two Knowledge Base articles that explain how to migrate logins between servers.
For your SQL Server 7.0 to SQL Server 2005 instances, there is the article “How to transfer logins and passwords between instances of SQL Server“. And then for SQL Server 2005 through SQL Server 2012, you can use the identically named, article “How to transfer logins and passwords between instances of SQL Server“. I’ve used these posts in the past with minimal issues (it’s been too long ago to remember what those issues were).
Summary
Sometimes when you’re working with SQL Server, complicated issues have simple answers. In this case, you can go to Knowledge Base and pull down a couple stored procedures to move logins between servers. When you need to move logins, do you use these articles, or do you have another process?
Strate SQL 
Moving logins is easy. Moving permissions is not.
LikeLike
Permissions are definitely a bit more tricky. The upside, all of that information is stored in metadata. If I can find it, I’ll post a query that I use to script out permissions for users.
LikeLike