Basic Security Question for remote app

I'm designing a C# winforms app that will be used by 500 customers/companies. The customers will use the app to edit data for their company on the remote SQL Server. What's the best way to manage security in terms of logins/passwords? Should each user have a different login? (probably not) Or should the application have it's own login/password that is encrypted and the user never knows what the login/password is?

Also, is it necessary to use Web Services for this application? Any other thoughts/comments are very welcome.

If there are 500 DIFFERENT companies, would you want a user for companyA to be able to change information for CompanyB?

If not, then using a common login/permission set is NOT a good idea and individual logins seem necessary. It seems like the question then becomes "How to mangage a large number of logins/permissions?"

Perhaps more details are would be useful to fully understand your scenario...

|||Here's some more detail:

Company Table
CompanyId
CompanyName

ProductTable
ProductId
CompanyId
ProductName
Price

Then, we ship out applications to all the different companies.
The application should know what their CompanyId is. In the C# application, there will be a grid that displays all of the Products for their CompanyId and allows the user to update those products (for example, change the Price for a given product).

At this point, I'm just kind of at a loss for the best way to design this in terms of SQL Server Security and logins/passwords, etc. Hope this is not too vague.
|||

This will prove to be an interesting challenge. I can already imagine some interprising soul for a company 'exploring' the products for other companies -and who knows, even changing prices...

The 'safest' way to control data access (keeping each user to his/her own company) is by knowing what user is attempting data access on each query execution. Without a company specific login (or user specific login), you will have a difficult (perhaps even impossible) time keeping users out of each others data.

Yes, on the surface, the application can appear to take care of it. But are you willing to 'guarantee' all companies that their data is safe from prying eyes and safe from accidental or intentional alteration.

The database 'should' know, with certainity, who (user or company) is accessing data. I think that you have no option but to assign individual logins to each company/user (all users for a company 'could' use the same login).

You will need a process to manage Logins, adding users, resetting lost passwords, disabling or removing users. And a way to map a login to a CompanyID. Once someone logins in, the CompanyID value is supplied to be used in WHERE criteria. I wouldn't accept a CompanyID from the application.

Personally, from the data professional perspective, I wouldn't trust my career to expecting the application to control all access.