The problem seems to be that the underlying objects for the query are system views.

If you take an exact copy of sp_spaceused and create your own copy in the master database then it simply doesn't work outside of the context of the master database even though your code is identical!

It seems that the original sp_spaceused, being on the sys schema treats other sys views as if they were local where as the bespoke sp_spaceused being on the dbo schema treats them as if they reside in the master database.

Perhaps the best way of dealing with this issue is to capture the SQL thrown at the DB by the built in report "Disk Used By Top Tables" and put it in the MODEL database and all user databases.