William (Bill) Vaughn's Musings

Next, grant rights on the specific stored procedures you want your applications to use.
I generally create SQL Server logins (after having enabled mixed-mode security) that only the application code uses. I don't try to create individual accounts for each person--it's an administrative nightmare. The User is only granted rights to the application if they can log into windows using Windows SSPI authentication handled by the domain.

Once the application connects, it gathers additional information from the user's windows credentials or it gathers another UID/PWD and revalidates this against a custom SQL Server validation SP. The UID could be "admin16" or "salesperson" or a user's name (but not usually). These credentials coupled with the attributes associated with the "person" or role as determined by the validation SP tells the application what this person is allowed to do with the application. If I validate the user I have the SP generate a dynamic security token and pass that to any SP I call to revalidate the user. Because I designed all of this myself, there is less of a chance that a nationwide-hacking attack can penetrate it. It's probably not any more secure, but it's different enough to make it harder to crack.

If someone fires up Access or VB/VB.NET they won't be able to connect to the server unless they know the SQL Server Login/Password used by the application. Since database objects are not made visible to normal users they can't see views, tables, stored procedures or other objects. Those who have seen (or compromised) the application credentials can try to run one of the exposed SPs , but since the SPs also require valid credential tokens (which are generated dynamically by the application), they can't do anything. If they hack the validation credentials somehow, all they can do is run that specific SP--they also don't know that I log unsuccessful accesses to the database as well as each invocation of the SPs--and who made it. The token validator can also include valid MAC addresses or other means to re-re-re-validate the user--at least I can track them down later...

Yes, there are lots of other things you can do--depending on how secure you want your system to be and what the data is worth. If it's the company payroll you're protecting, you'll do more to hide the data than if you're retrieving a list of employee parking spaces but less than a list of everyone's SSAN. As data becomes more important, it's critical to add layers of protection that makes casual or hacking more difficult. Private data should always be encrypted in place--or not stored at all. Unencrypted data should never leave the physical security of the site--even if it's just the SSANs of 240,000 Veterans.