Follow up on Issue: Server principal is not able to access the database under the current security context
SQL Server operates on the concepts of Logins and DB Users.
A login (server level) can map to a single database user. The name of the db user can be the same, or can be different. A login is mapped to a user via an SID (security ID)
Often, when databases are moved around, this login-user mapping gets lost because a different login in a different server or instance will have a different SID. And this causes grief not just to users but also to DBAs.
Couple ways to fix this:
This is marked for deprecation, but you can still use this.
3 options for sp_change_users_login:
Auto_Fix, database user is mapped with same named SQL Server login. It may also create login, if not present
Report, it lists the orphaned users and their security identifiers (SID)
Update_One, it links the specified database user to an existing SQL Server login
EXEC sp_change_users_login @Action=’Report’
EXEC sp_change_users_login @Action = ‘Update_One’
UserNamePattern = ‘dbusername’
@LoginName = ‘loginname’
When there are a lot of users, can use a cursor.
2. ALTER LOGIN – better way or recommended way
ALTER USER dbusername WITH LOGIN ‘loginname’