How does it look in MetaMask?
SIWE is a feature that developers can choose to make available to users of their dapps. Accordingly, you won't find it on every dapp you visit, but only on those where it's implemented.
Signing in with SIWE looks similar in MetaMask to many other signature requests:
What does this mean? Well, you're essentially just signing a transaction to demonstrate your identity. Nothing more, nothing less. Using your private key to sign a transaction proves that you're the account holder, and have the authority to use that account on the dapp.