How to fix orphaned users in SQL Server.

Orphaned users in SQL Server are database users without a corresponding login in the SQL Server instance. This can occur when a user is deleted from the SQL Server instance without first removing the associated database user. To fix orphaned users in SQL Server, you can follow these steps:

1. Identify the orphaned users: Run the following query in SQL Server Management Studio to identify the orphaned users in your database:

USE your_database_name
go

EXECUTE sp_change_users_login 'Report' 
go        

1. This will list all the orphaned users in your database.
2. Re-map the orphaned users to a login: To map an orphaned user to a login, use the following syntax:

USE your_database_name
go

EXECUTE sp_change_users_login 'Auto_Fix', 'orphaned user' 
go        

1. Replace orphaned user with the name of the orphaned user.
2. Alternatively, you can use the following syntax to map all orphaned users to a login:

USE your_database_name
go

EXECUTE sp_change_users_login 'Auto_Fix' 
go        

1. This will map all orphaned users in the database to logins with the same name.
2. Verify the user mapping: After remapping the orphaned users, you should verify that the user mapping is correct. You can do this by running the following query:

USE your_database_name
go

SELECT * FROM sys.database_principals WHERE TYPE = 'U' 
go        

1. This will show you a list of all database users in the database, along with their associated logins.

By following these steps, you can fix orphaned users in SQL Server and ensure that your database users are properly mapped to logins.