|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Prevent Double loginHi Everybody
Assume a user (A) opens my application on one computer and moves to another computer and opens the same application with the same user name (A). This will result in the user login in twice. Is there any of preventing this from happening when using MS SQL Server? I am using SQL Server 2K. Any assistance will be highly appreciated. What if the user doesn't log out? Will he never be able to login again?
Depending on the use of your application, you can have a 'last login date/time' to control that. This will work if you know beforehand that your users don't switch machines all the time. manK wrote: Show quote > Hi Everybody > > Assume a user (A) opens my application on one computer and moves to another > computer and opens the same application with the same user name (A). This > will result in the user login in twice. > > Is there any of preventing this from happening when using MS SQL Server? > > I am using SQL Server 2K. > > Any assistance will be highly appreciated. > Thaks for the reply.
I was thinking of creating a table to store users who are currently logged on and users log out explicitly from the application. After log out the user gets deleted from the table. However, after you mentioned it, i realised a system could crash and end up without deleting the user from the logins table. It was not clear how 'last login date/time' may be applied. Would you please explain further. Show quote "Hugo Madureira" wrote: > What if the user doesn't log out? Will he never be able to login again? > > Depending on the use of your application, you can have a 'last login > date/time' to control that. This will work if you know beforehand that > your users don't switch machines all the time. > > manK wrote: > > Hi Everybody > > > > Assume a user (A) opens my application on one computer and moves to another > > computer and opens the same application with the same user name (A). This > > will result in the user login in twice. > > > > Is there any of preventing this from happening when using MS SQL Server? > > > > I am using SQL Server 2K. > > > > Any assistance will be highly appreciated. > > > That depends on how the system is used.
Case 1: The user log in every day once. If you have the login date, you can prevent the user from logging in on different machines on the same day. Case 2: The user log in several times a day. This way you should keep 'login date/time' and 'login machine'. If the system crashes, the user will be able to login on the same machine he did the last time. When he logs out you clear the login date/time and machine and he will be able to login on another machine. This is just an example. Hope it helps. manK wrote: Show quote > Thaks for the reply. > > I was thinking of creating a table to store users who are currently logged > on and users log out explicitly from the application. After log out the user > gets deleted from the table. However, after you mentioned it, i realised a > system could crash and end up without deleting the user from the logins table. > > It was not clear how 'last login date/time' may be applied. Would you please > explain further. > > > "Hugo Madureira" wrote: > > >>What if the user doesn't log out? Will he never be able to login again? >> >>Depending on the use of your application, you can have a 'last login >>date/time' to control that. This will work if you know beforehand that >>your users don't switch machines all the time. >> >>manK wrote: >> >>>Hi Everybody >>> >>>Assume a user (A) opens my application on one computer and moves to another >>>computer and opens the same application with the same user name (A). This >>>will result in the user login in twice. >>> >>>Is there any of preventing this from happening when using MS SQL Server? >>> >>>I am using SQL Server 2K. >>> >>>Any assistance will be highly appreciated. >>> >> manK (m***@discussions.microsoft.com) writes:
> Assume a user (A) opens my application on one computer and moves to This is not entirely easy to achieve with a modern application design. It's> another computer and opens the same application with the same user name > (A). This will result in the user login in twice. > > Is there any of preventing this from happening when using MS SQL Server? easier with a less modern, though. That is, today most applications work disconnected, so they connect only when the user submits a query. In older designs, the user connected and stayed connected. Although, even in designs of older days, applications typically connected the user more than once under the hood. It's a small thing to add a row to an application table when the user actually logs into the application, and check that he is not already in that table. The problem is, as Hugo pointed out, if the session is terminated in some irregular way. Then the user may be told he can't log in, because the system thinks he is already logged in. If the application is designed to have a master connection that is connected as long as the users is logged into the application, you can save data about that connection: spid, host name, login time etc. All this is data that you can find in master..sysprocesses. When the user connects, you look him up in the table, you also compare the data about the process to master..sysprocesses. If that spid is no longer around, or has a different login time, the data in the table should be ignored. Note that such a solution will not work out of the box on SQL 2005, since on SQL 2005, users cannot view data about other processes in sysprocesses without extra permissions. Another solution, that would work with a modern application design, is that the application every tenth minute updates the logged-in entry in this table. If the application crashes, and the user tries to log in from a second computer, he will only be blocked for a couple of minutes, as you would ignore entries that are older than ten minutes. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp Thanks a million for your enlightening contributions (Hugo and Erland) .
Making a decision of whether to prevent or not has become easier. Show quote "Erland Sommarskog" wrote: > manK (m***@discussions.microsoft.com) writes: > > Assume a user (A) opens my application on one computer and moves to > > another computer and opens the same application with the same user name > > (A). This will result in the user login in twice. > > > > Is there any of preventing this from happening when using MS SQL Server? > > This is not entirely easy to achieve with a modern application design. It's > easier with a less modern, though. > > That is, today most applications work disconnected, so they connect only > when the user submits a query. In older designs, the user connected and > stayed connected. Although, even in designs of older days, applications > typically connected the user more than once under the hood. > > It's a small thing to add a row to an application table when the user > actually logs into the application, and check that he is not already > in that table. The problem is, as Hugo pointed out, if the session is > terminated in some irregular way. Then the user may be told he can't > log in, because the system thinks he is already logged in. > > If the application is designed to have a master connection that is > connected as long as the users is logged into the application, you can > save data about that connection: spid, host name, login time etc. All this > is data that you can find in master..sysprocesses. When the user connects, > you look him up in the table, you also compare the data about the process > to master..sysprocesses. If that spid is no longer around, or has a > different login time, the data in the table should be ignored. > > Note that such a solution will not work out of the box on SQL 2005, > since on SQL 2005, users cannot view data about other processes in > sysprocesses without extra permissions. > > Another solution, that would work with a modern application design, is > that the application every tenth minute updates the logged-in entry in > this table. If the application crashes, and the user tries to log in > from a second computer, he will only be blocked for a couple of minutes, > as you would ignore entries that are older than ten minutes. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp > >  |
|||||||||||||||||||||||
Other interesting topics