MVC N-Tier Architecture
I personally think that what you have done here by separating the layers is better than including this all in one layer....


You're following the Single Responsibility Principle - Business Logic and data retrieval results in two different reasons for change and therefore I believe separating them in different classes is following this principle
Your IUsersDAL can be stubbed in unit testing as this is injected into UsersProvider - you can separately test data access and business logic
Reusing GetUserByUserNamePassword in numerous other business provider's means you don't have to write the same query again therefore you're promoting code reuse.  Your UsersDAL is basically a repository pattern for the entity User as this will be used for storing the numerous LINQ queries associated with data retrieval for that entity.  Maybe think object having repositories for aggregate root objects only.
What if you changed your data retrieval for a User entity from a LINQ query to accessing them from a web service - you never know what might change in your application as it grows and you wouldn't want to be directly referencing web services in your business layer, accessing them through a Repository pattern would be a cleaner way of doing it.


I think developers sometimes doubt the extra layer due to the advantages of using an ORM and Linq as like you have done as you can create the data access retrieval within one query.

ORMs like Entity Framework are Unit Of Work and Repository patterns in themselves this is why having an extra layer to contain the query seems to be trivial, however I believe you're merely extending the repository as you're encapsulating data retrieval logic specific for an entity.