My specific use case is the need to run SQL Compare on target tenants/environments and have it generate a snapshot and publish it as an Octopus artifact. (We can't use the DLM automation suite for this specific product yet and have to spend time doing SQL comparisons as things may be different at different sites)

Right now you can do this through the script console, but would have to edit the script each time and substitute the server / database name, as it may be different per tenant/environment. A better way would be to create a project for it with deployment steps to run the commands and collect the artifacts. It just seems a little weird to keep "deploying" a release to run a couple of scripts.

It would be nice to be able to store a script and have it be able to access variables scoped to tenant/environment and run when needed.

Very often I need to execute some little tasks in remote computer. Old way is to open RDP connection and do tasks manually or run script on target machine.
Script console is great way to do the same thing without opening RDP connection. Using Script Modules in Script console (or kind of my latest favorite scripts) would be great feature especially doing repeatable tasks on target machines without writing/copying same script again and again.

And of course it is easier and faster to test and debug script modules.

Script Console provides good opportunity to run some maintenance or provisioning scripts to the machines used for deployment. Unfortunately currently there is no way to save such scripts to be reused later on.

Use case:
Run bash script when the new role added to add the users under which the application will be running.

This is fine workaround for now to copy-paste the script each time from the version control, but if the script is a bit more complex and contains some environment specific variables to replace it introduces some huge risk and human-mistake possibility.