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:

1. sp_change_users_login

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’

Be Sociable, Share!

• 
• 

Related posts:

1. Issue: Server principal is not able to access the database under the current security context …
2. How to script out your database objects (Database Publishing Wizard) …
3. Free Database Schemas …
4. How to Drop All Stored Procedures in Your Database …
5. Troubleshooting Database Mail …
6. Installing SQL Server Denali CTP3 Weird Issue – Invalid Handle …