When a Microsoft SQL Server backup is restored, it often includes the users that were associated with the database at the time of the backup. If the backup is used to restore on a new server, then there’s a good chance that the database users will be “disconnected” from the SQL Server users (if they exist at all). Fortunately, restoring access is simple.

Recreate the user

Within Microsoft SQL Server Management Studio (SSMS), connect to the SQL Server instance. Firstly, take a look at the database’s users:

1. Navigate to the database
2. Open the database’s Security folder → Logins and note the users within this list.

Users within this list should have an associated login in the root of the SQL Server instance (as depicted by SSMS) as authentication (e.g. password authentication) occurs at the SQL Server instance level, not the database level.

Now, navigate to the root’s Security folder and view the list of users. If the database user doesn’t exist here, then they won’t be able to log in. Create a user by:

1. Right-clicking on the root-level Security → Logins
2. Select New Login…
3. Enter the same username as the database instance.
4. This should use a SQL user name and password. This password will be used to authenticate with the SQL Server instance.

Fix the username mapping

Creating a user here will not associate with the database’s user instance (even if they’re the same user name) as the security identifier (SID) of the users will not match, essentially creating an orphaned user in the database.

To fix this issue, simply run the following:

ALTER USER orphaned_username WITH LOGIN = corrected_username;

Run the above with the associated usernames and the login will now work.