Fixing orphaned users in a SQL Server database

Mapping of a SQL Server login's SID to a database user is stored in the database. When a database is attached or restored from another SQL Server instance, database user and the SID of the corresponding SQL Server login on another SQL Server instance don't match. The database user will not be mapped to any login and will become orphaned.
If the corresponding SQL Server login is dropped the database user also becomes orphaned.

You may get some of the following error messages:

Msg 916, Level 14, State 1, Line 1
The server principal "Login_Name" is not able to access the database "Database_Name" under the current security context.



Cannot open user default database. Login failed.
Login failed for user ‘User_Name’. (Microsoft SQL Server, Error: 4064)

To check for all orphaned in a SQL Server database:

EXEC sp_change_users_login 'REPORT'


To fix the orphaned user, run sp_change_users_login with the UPDATE_ONE option and specify user and login to be mapped:

EXEC sp_change_users_login 'UPDATE_ONE','John','John'


To see how to fix Orphaned ‘dbo’ User in a SQL Server Database, read related blog post:
Orphaned ‘dbo’ User in a SQL Server Database

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">