It doesn't help as much as you'd think. With a buffer overflow on the stack, you can overwrite the stack's return address to point wherever you want, and overwrite a bit further to set the arguments to whatever you want, as well as any local variables on the stack. That depends, obviously, on what code is already there, but there's a lot you can do. For instance, if the system() libc function is linked, you can overwrite the return address and arguments to call system("some arbitrary shell code").

Check out https://en.wikipedia.org/wiki/Return-oriented_programming for more information.

You don't need to execute the stack. Just use ROP to fill the stack with return addresses that point to "gadgets". Gadgets are basically a single assembly instruction at the end of a function like XOR EAX,EAX and then a RET. Every time a RET is executed the CPU will jump to the next gadget. There are usually enough gadgets inside a program to basically do anything you want.

https://www.exploit-db.com/docs/28479.pdf

DEP only makes attacks harder, not impossible. It's not a magic bullet for a couple reasons: it's opt-out on non-*BSD operating systems so software might not be using it, and it can be circumvented with things like return-to-libc attacks.

I would like to add that there are starting to be mitigations against attacks modifying control flow, for example CFguard under Windows (which will probably be extended to cover ROP also) but like DEP an ASLR those are only mitigations, not something that would make it useless to care about buffer overflow anymore.

In order for DEP to render such attacks impossible it would need to have permission bits at the byte level. DEP on most OSes is page-level, so the stack page is marked as non-executable, which turns an attack from executing code on the stack to executing code from the text segment - either through ROP gadgets or return-to-libc.