Home / server / Questions / 176520
Accepted
epotter
Asked: 2010-09-01 06:31:51 +0800 CST

In SQL Server, how do I create a login for an existing user?

  • 772

I restored a database from one SQL Server instance on to a different SQL Server instance. The database allready has users added to it. But they don't have Login's for the new SQL Server instance.

I know how to create new logins for new users, but how do I create Login's for existing users?

sql-server login

4 Answers

  1. I always run the sp_change_users_login command with the 'Update_One' action to re-link the database user with the server login:

    sp_change_users_login 'Update_One', 'database user', 'server login'

    • 13
  2. Best Answer

    The problem is that the restore brings back the db users from the original server instance but the new instance typically knows nothing of those user logins. You'll see them under Security at the db level, but they don't have corresponding server level logins.

    You need to reattach the database users with server logins on the instance on which you restored the database.

    There are a couple of ways to go about it:

    1. Create new logins for them on the new instance. You'll then have to remove them as database users on the new instance and add their new logins. This seems odd given that you can create the same login names, but the SID's (security identifiers) will be different and that is what SQL uses for user identification. This is easy enough for one or two logins.

    2. If you want to maintain the same users, keeping the same SID's, across instances then use the sp_help_revlogin stored procedure. Copy the code from the link which will create the necessary stored procedures and run it on the server that you want to copy users from. It'll generate a SQL script that you can run on your target server to create the same users carrying over SID's, passwords, everything. This is the way to go if you have a lot of database users you need to reconnect on the target instance or if you don't know passwords to one or more SQL Logins on the source instance.

    • 10

  3. As of SQL Server 2012, there's a T-SQL command to assign existing users to new logins. Create the login, but don't try to do User Mappings (it would fail). Then execute:

    use <existing_database>;
alter user <existing_database_user> with login = <newly_created_login>;

    This will map the existing login to the new user. SQL documentation here: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-user-transact-sql?view=sql-server-2017

    • 6

  4. Here is what I found.

    As squillman expained, the users get copied over with the database, but the logins do not. There is a store procdure called sp_change_users_login you can use to resolve the issue. The steps to do so are detailed in this aritcle. Details of using the store procdure can be found on this page.

    • 2