I’m currently involved with a couple of projects where we have needed to migrate databases from one SQL Server to another and needed to do so more than once.
Prior to the first copy, we used a script to load the users into SQL Server and then restored the databases and had no problems.
Later on, after changing some of the permissions to databases we had need to remigrate some of them over. However, we found that users could not access some databases and when we tried to add them we got the following error;
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'pidev' already exists in the current database.
It was looking very like we would need to go through each database and remove access and then regrant it all; a labourious process. A quick Google found me a page from Julian Kuiters which introduced me to the ALTER USER command. The following code will run against the selected database and tie together the SQL Server user with the user in the database without any other work;
USE [GPNL]
GO
ALTER USER pidev WITH LOGIN = pidev
GO
The first highlighted element needs to be changed to the database you want to update the user on and then the following two entries are the usernames which need to be linked (these should be set to the same value).
Update: if your usernames contain a space (e.g. I Grieve) then wrap them in square brackets: e.g.
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
If you get the following error:
This means that the user you’re working with does not exist as a SQL Login, but only as a user against the database. Create the SQL Login and re-run the script.