How to connect an existing SQL Server login to an existing SQL Server database user of same name

137
votes

Is there a SQL Server command to connect a user of a single database to a login for the database server of the same name?

For example:

Database Server - Default Instance
Database: TestDB

Server Login - TestUser
Existing user on TestDB - TestUser

If I try to make the TestUser login a user of the TestDB database, the "User, group, or role" already exists.

Does anybody know of an easy way to assign the DB user to the server login?

3
sqlsql-server

3 Answers

257
votes

The new way (SQL 2008 onwards) is to use ALTER USER

ALTER USER OrphanUser WITH LOGIN = correctedLoginName;
6
votes

I think it's sp_change_users_login. It's been a little while, but I used to do that when detaching and reattaching databases.

1
votes

I've used the sp_change_users_login procedure to accomplish, I think, that scenario.