165

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?

Improve this question

3 Answers 3

Reset to default
314

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

ALTER USER OrphanUser WITH LOGIN = correctedLoginName;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Since SQL Server 2005. Gratzy's link refers to this actually.
I just used this in SQL Server 2012 and I can confirm it works
Confirmed as still working on SQL Server 2019.
And confirmed in SQL Server 2022
7

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.

Improve this answer

3 Comments

If the SQL login was created using a Windows user login, then sp_change_users_login will not work--I ran into this today, leading me to this question. msdn.microsoft.com/en-us/library/ms174378.aspx
this method is deprecated now in favour of the ALTER USER method, above
@liam did you really just down vote this answer that is 8 years old and reference the accepted answer that is also 8 years old which already contains the information that its deprecated?
1

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.