When a database is moved to a new server, existing database users will not have access to the database on the new server. Attempting to add the users to the database on the new server results in an error message saying the user already exists in the database.
To fix this you have to drop the user from the database and then add the user back to the database.
use TEST_DB
go
drop user test_user
go
Occasionally we receive the following error:
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
Run the following commands:
use TEST_DB
god
select CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER from information_schema.schemata
where schema_owner = ‘test_user’
go
Change the owner of the schema using the following command:
Alter authorization on schema::db_owner to dbo
Go
Then rerun the drop user commands and add the user back to the database.
use TEST_DB
go
drop user test_user
go