I have an application that uses a small SQL Server database.
What I need is to prevent the access of an amount of users to the database.
At the beginning I want to limit the access only for 2 users but I want to be able in the future to grant the access to more.
It's a windows client-server application.
I am thinking of giving a session ID when a user log in. Every time the client uses the database I'll check the session ID if is valid.
When the client try to log in the 2nd time I'll give him another Session ID and I'll destroy the other one.
I have also to limit the session ID numbers to 2 for the beginning and I'll ignore users that are not in the top 2.
What do u thinck about this?
the solution you suggested will work, but the problem with it is - it requires maintenance module to manually disconnect users those who have accidentally or by mistake closed the application/machine without proper logout.
SQL server allows you to create unique application objects and lock them during execution. I think that can be an alternative solution to the problem. check sp_getapplock on BOL. the best thing is - it automatically releases the lock as soon as the connection is lost.