Assign login to SQL user without login

Question

Environment: SQL Server 2012 Express Edition and Microsoft SQL Server Management Studio. All users are local SQL users, NOT Windows authentication.

I attached a database file which came from another SQL server. In the "Server/Databases/Imported Database/Security/Users" node it contains the users for the database, but their types are "SQL user without login". It seems that they cannot log into this SQL server.

At first I thought I could create users with the same ID in "Server/Security/Logins" and map them to the users in the imported database. It created users but with errors (users already exist) and the states of the users in the imported database are still "SQL user without login"

I searched Google but all the answers were some complicated scripts. If this is a common scenario, why aren't there any simple GUI for it? What is the simplest way to make the users in the imported database be able to login with the same credentials?

Milena Petrovic · Answer

There's no GUI, you have to do it manually.

First of all, you have to differentiate SQL users and logins.

A login is related to the SQL Server instance and has permissions on the Server. One login can be associated with multiple users.

A user is related to a database and has permissions on a specific database.

Principals (Database Engine)

When you attach a database created on another server, you transfer the logins associated with it on the original server, but the SQL logins are not transferred, as they are defined on the Server, not the database. The database backup, or MDF and LDF files don't contain the info about SQL logins, only about database users. Therefore, the error message.

Use the steps and script given here to transfer the original logins to the new SQL Server instance: How to transfer logins and passwords between instances of SQL Server

Assign login to SQL user without login

One Answer

Add your own answers!

Ask a Question