If the installer is an MSI installer and it was written to allow per-user installation, then it should "just work". When your non-admin user runs the installer, it will install in its per-user mode. If your admin account ran the installer, it would usually have a choice to install for everyone or just that user.
The trouble is not all installers are MSI's. And not all MSI's are written to allow per-user installation. Depending on the software, it may not even be possible to do a per-user install regardless of the type of installer.
So ultimately, the set of apps this will work with is pretty small. Most developers assume the user will have administrative privileges on the machine at install time and that they will want all users on the machine to have access.
I thought regular user (non-admin) accounts couldn't install any software, even for themselves only. – martineau – 2011-02-20T08:01:41.190
They can by default on a non-domain joined machine as long as the installer doesn't want to write anywhere they don't normally have write access to. There are additional ways installers can be locked down with Group Policy in a domain environment. – Ryan Bolger – 2011-02-20T17:17:47.473