Traditional Login and User Model

The important principal is that both the login (in the master database) and the user (in the user database) must exist and be related to each other. This means that the connection to the user database has a dependency upon the login in the master database, and this limits the ability of the database to be moved to a different hosting SQL Server or Azure SQL Database server.

Contained Database User Model

In the contained database user model, the login in the master database is not present. Instead, the authentication process occurs at the user database, and the database user in the user database does not have an associated login in the master database....To connect as a contained database user, the connection string must always contain a parameter for the user database so that the Database Engine knows which database is responsible for managing the authentication process.

By reducing the ties to the instance of SQL Server, partially contained databases can be useful during failover when you use Always On availability groups.

Creating contained users enables the user to connect directly to the contained database. This is a very significant feature in high availability and disaster recovery scenarios such as in an Always On solution. If the users are contained users, in case of failover, people would be able to connect to the secondary without creating logins on the instance hosting the secondary. This provides an immediate benefit.

Conclusion

So if I understand this correctly, Sitecore made a good decision to do this since it simplifies the way they distribute the databases for on-premise and Azure instances. Given that this is a feature promoted by Microsoft, I recommend you help provide articles/documentation to the Database Administrators. I don't imagine Sitecore will provide an alternative approach so education will be your best stategy.

Can we use SQL users after installation?

Why contained database users?

You should note, based on the details from that question, that Contained is an absolute requirement when using Azure. If you plan on going to Azure SQL, you will need to use contained database users. This is one of the reasons it has been introduced so that we can move towards practices that are current and "cloud-ready".

"DB has to be contained" error

Regarding your restore issue, you likely were restoring databases that already had contained users in them, which is why you would receive that error.