I was very concern about what the first post would be on my shiny new blog, actually second post because the first one was kind of a preamble and self justification, but that doesn’t really count as a post… but any who… I was under the impression that this post had to be something really profound and meaningful… until I realized that probably nobody else is going to read it but me xD. So that took off a lot of pressure and I started to think about what was the most common piece of code that I always find myself looking over and over again because I just couldn’t remember it.
Undoubtedly it was the assign login to user thing. This very simple line of code keeps slipping my mind every time I need it. The first time I encountered this problem I came across this great post that had a solution right away, but the post is quite long and has a couple of different ways to use the command but I always need just this one, so I would store the link in delicious with a little comment on the title like: (remember to use 3rd option). Not very stylish but it did the job, up until now. Maybe later I’ll post a more detail guide about the error.
So here we go, the following command will solve all your problems. Both the login and user must already exist in the server and database respectively.
USE [YourDB] EXEC sp_change_users_login 'update_one', '[user]', '[login]'The most common scenario where I find myself needing this is when migrating a very large database update from a development server to a quality server, where the easiest way of synch all the modifications is to manually detach/attach the .mdb file to the quality server, where the logins are already created but the association breaks with the migration.