Notifier Pro Plus (paid version, not Notifier Pro - free version) does allow you to pick any installed app and set a notification for it. If the app has it's own notification, it plays both, unless you turn off the app's notification. For apps that either have no notification, or do not allow you to change the notification tone, Notifier Pro Plus allows you to set any app with any notification you have available.
I have installed Zedge, which has TONS of free ringtones and notifications. However, it does not have some of the things that I wanted to be able to really customize the ringtones I wanted for certain people.
I have also installed Ringtone Maker. This little app allows me to take any sound file, edit it to only have the exact sounds I want, amplify it to be loud enough to be heard, and save it for access by an app or Notifier Plus Pro.
So, I have made a couple of notifications & ring tones that I really like. For example, when someone calls that is in my address book, it plays a few loud guitar riffs from a Chuck Berry tune. When my girlfriend calls, it plays the opening vocal from James Taylor's "Whenever I see your smiling face, I have to smile myself, because I love you!" When my ex-wife calls, it plays the Mission Impossible Theme. When my Doctors call, it plays a siren. My lawyer's calls are announced with the opening bars from Pink Floyd's "Money." Family calls are announced with the Pointer Sisters singing "We are family." My sms messages are announced with the whistle from the Old Spice commercial (downloaded from Zedge). Facebook messages have another whistle notification. Breaking News from CNN & USA Today are announced by a few notes from one of those "breaking news" tunes you hear on TV or the radio.
I have managed to set up all my communications so that I recognize the source without having to look at my phone, and THAT is exactly what I wanted. So, I heartily recommend Notifier Pro Plus as the path to setting a ready tone for any app, Zedge for downloading tons of ringtones and notifications, and Ringtone Maker for tweaking any sound file to fit the length and volume you want.