Author: Akash Heranjal
Problem:
The database backup is taken from the development environment and the
BAK file is restored on the production server. On restoring the
database from the BAK file, the database users with DB Owner rights are
also restored along with the database. But checking the security
>> user list in SQL server does not display these users. The SQL
server does not allow deleting the users from the database manually as
they own objects in the DB. SQL Server does not allow creating the DB
user and assign DB Owner rights for the user to the restored DB.
Cannot delete the user as it own objects in the database.
Solution:
The database users as explained in the problem above are called
“Orphaned Users”. Run the following query in “Masters” DB to sync the
Orphaned User.
sp_change_users_login 'auto_fix', 'User Name'
User Name here is the name of the orphaned database user.