It's fully clear what your question really is, but there is no way you can lock out users from doing things only from the application and never be able to get there through SSMS or Access. Not with a two-tier application installed on user machines.

What you can do is to make it difficult. You can use an application role which you grant all permissions. The application issues sp_setapprole to activate the application role. The application role requires a password. But the password must be stored somewhere, for instance in the executable. Which the users can access and tear apart as they like.

Thus, this is security by obscurity. You will keep the non-savvy out, and prevent them from doing silly things. But not the smart and evil-minded guy.

Beware that using application roles has some effects on connection pooling. Either don't use pooling, or set the role with a cookie and unset before you disconnect.