It has been several years since I've been involved in SQL development. I recall that some time back, there was a best security practice to always have app code call stored procs that had been granted execute to Public.
Would someone elaborate on best practices today and the rationale?
Thanks,
Sam
I've never heard of a best security practice of granting execute to public. Honestly that sounds like just the opposite. I can't provide any one-size-fits-all answer to the other part of it, but I would urge you not to grant execute, or other database-specific permissions, to "public".
very bad idea I would say. What kind of app is it? web app?
Web app. If I recall correctly, it was a suggestion to deny access to the db if someone hacked the server. Only SPs could be run.
So what is the best practice today? Windows authentication access?
your web app should be running using an app pool. And the identity of that app Pool (In advanced settings if it is IIS) should be an actual active directory user
you give execute permissions to sprocs etc to that user. usually folks give public access perms bcs it is easier.
One way would be to create a SQL login without a password, grant insert, update, delete, select to it. Create another login for the application with a password and only execute on the db and impersonate on the first login. Create stored procedures with execute as the first login. This limits what the application can do and prevents ad-hoc queries using the credentials the app has.
If the SProc is created "by a user with those [table] permissions" is there additional benefit to the "impersonation" method?
We only use impersonation where the SProc has to use dynamic SQL ... but I'm always keen to learn a new trick 
We have, internal, web apps where each individual Windows Logon User has AD permissions.
Then we also have external-facing web apps where there is a single login (for the APP), as you described, and if those users need some sort of login (e.g. to maintain personal preferences / shopping carts / whatever) there is a "soft logon" maintained by the APP itself
Come again? I am not understanding this part?