The 1GB limit is per instance of SQL Server Express. Each instance can support multiple databases and multiple users.
Now, that does NOT mean that when you open Task Manager and see he Working Set creeping up to 1GB something bad is going to happen. You'll see the memory for SQL Server exceed 1GB and can go much, much higher than 1GB.
SQL Server has hundreds of caches and stores for various things. The 1GB limit only applies to one thing, the
buffer cache[
^].
What happens when the 1GB limit is exceeded? I have no idea. I've never even come close to exceeding it on SQL Express.