If you need really tight control enforced at the data level. For example extensive auditing. If you have some users that have a need to access the data database directly.
If security is that tight you typically don't even expose the database directly. You have a service and the client must get data from the service.
If the login and password is in the client application then it can be hacked. If a domain you can use integrated security.