Is there any guidance on actually using the SecureString in a secure manner? I don't see there being any way to even create the secure string in the first place, as you are going to need it to be typed in from a textbox at some point.
It's all about reducing the attack surface. It won't magically make your application 100% secure but it certainly helps.
Even if the contents of the string has to come from user input it's still worth using SecureString if the value contains sensitive information. When your application reads the contents of the textbox, put the value into a SecureString at the earliest opportunity. As Mehrdad says, in this case it's not 100% secure but it is more secure than not using SecureString.
SecureString purpose is to encrypt the storage of the data in memory. This protects against clear text memory scans. This can be even more important if the portion of memory holding the string is moved to a swap file during paging operations.
It's a bit like a steering wheel crook lock for a car. It won't stop your car being stolen by someone who is determined, but it certainly puts off opportunistic thieves.
The string is easily available to anybody with debugger access to the machine, using tools such as hawkeye. Indeed, the community notes on MSDN make this clear.