In which scenario user runs his program on stored process server rather than the workspace server

[SASKiwi]

Any time you run a SAS stored process, typically via a SAS web interface, it runs on the stored process server. SAS EG and SAS Studio sessions use workspace servers.

[Anand_V]

When you wish to have a SAS program stored on a centralised server location and executed by various applications you can use a stored process. It is usually executed by a stored process server ran using sassrv ID which is kind of batch user.

[Sajid01]

SAS stored processes through their web interface provide option for self service analytics/reporting.
In addition, one can use Rest API to execute the STP and get the result as XML or json file.