I am constructing a dialog which prompts user for username and password to login to an SQL Server database. If another user on a different machine try to login with the same username, he will be ask if he wish to log out the old login first. If he choose yes, the old login will be logged out. My problem is that how can we know whether an SQL Server login is in use (has been logged by a different user)? And are there any ways to log out a logged SQL Server login?
Look at using sp_Who, or sp_Who2, to see the logins currently connected to the server. If you find a name you want to disconnect, take the spid and issue the KILL command. Not neat, but it will do the trick.
Wouldn't you prefer to know if they "log out" of the application? This likely will not equate to a SQL Server login being maintained. For example, look at hotmail. I open hotmail and pull mail. Then I browse my favorite website, which is www.sqlteam.com of course. Shortly thereafter, I decide to look at all my new SPAM graciously sent to me by the local porn providers. I don't have to relog in. I didn't maintain a connection to the database though. WOW How does this magic happen??????
They have a kewl little table called login_history (or some such nefarious object) which identifies the IP Address and a session key or something which is probably stored on my computer in one of those horrible cookies that will destroy your computer and walk off with your children.
If I login from another computer, it says "Who are YOU?". If I destroy my cookie, the cookie monster eats it, or I log off my internet browser, I have to magically log back in.
Yep....application security is the way to do it. It's a marvelous thing really. Those tables will help you though. Ask them for the SSN, birthday, and there most commonly used PIN number also to store in there.