From Sitecore 9.0 onwards, the database users changed from being traditional SQL logins to “contained database users“. With the exception of the Shard Map Manager and Collection shard databases, the users for each database are contained within the database itself. The users are portable with the database and creating and updating them is different from the “old” way.

An example of where this might be needed is creating a secondary reporting database for a reporting rebuild. If you deploy the DACPAC you will need to create a user. The SQL for this is:

CREATE USER reportinguser WITH PASSWORD = 'new_password';

To change the password:

ALTER USER reportinguser WITH PASSWORD = 'new_password';

To connect to a contained database in SQL Server Management Studio, enter your contained database username and password, then click the Options>> button on the SQL Server login modal and choose a database.

This way you can test your connection for troubleshooting purposes.

Also, slightly off topic, but for the secondary reporting database you can assign dbo role to the reporting user with sp_addrolemember

exec sp_addrolemember 'db_owner', 'reportinguser';