Jeff Moden (6/14/2012)

---

One of the biggest problems I've found is just how high the priv levels are for application logins. Like it or not, applications shouldn't have more than PUBLIC privs, IMHO. This can be accomplished fairly easily through stored procedures by making it so the procedures have the privs and the users have EXECUTE privs on the procs. The user don't have privs to even read from a table. This has been made a whole lot easier using the "DB_Executor" role which has been made possible as of 2005 by being able to grant EXECUTE privs to a DB role (it seems that a lot of people have adopted the name "DB_Executor" for this role).

Ironically, the use of xp_CmdShell can be a good measure of whether or not your system is actually properly locked down or not. In a properly locked down system, individual users can't even see tables, never mind run xp_CmdShell yet the stored procedures called by the users can use it.

So far as I'm concerned, a system isn't properly secure unless the only people that have any privs other than PUBLIC are the DBAs. I'm not saying that you don't need encryption of things like SSNs because you still have to protect against possible internal threats but there's really no excuse for having the potential of external threats.