You can use a session object. If you want to continue to use session, check this link. It has better ways if implementing the same. Also, it will be not of much use if you store session itself in SQL server. In that case, it will be better to store it in a different session user table as a JSON string maybe.
If you have low number of users in session at all points in time, like it is an application internal to some company, you can use Flyweight design pattern with a dictionary behind a singleton to bring the
Dictionary<string,object> and get the data at all times. Check the singleton sample here.
If you want a scalable application with high number of users, check the following as well.
You can use memory cache as Object Cache also under System.Runtime.Caching, by user key, and if it is not in cache, bring from database.
You can also use out of process caching like redis and memcached, or AppFabric Data Caching, if you are on windows server environment.
Then, you can have noSql databases, like mongoDB, for storing only session data on the server. Clear the session data if no activity after some time.