If you need really tight control enforced at the data level. For example extensive auditing. Auditing is not much good if several users share the same account. 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.
At the database you can have pretty fine controls. But row level control is a bit of work. A database is not a good tool for business rules. Business rules and detailed security are typically enforced at the application level.
You can have a mixed mode where there are some stored procedures used by admins and you want to track which admin. And you may give read only access to users that generate report directly.