MS SQL 2014 get SPIDs of users
Posted on 2016-11-04
I have an application that uses MS SQL Server 2104 as its database engine. Due to government regulations I need to start tracking the amount of time users spend working each day. They come in each day, log into an application and then log out at the end of the day. In addition to the user ID and log in and out times I was thinking of adding the SQL SPID of each user so later I could investigate any locking issues. How do I go about getting the SPID once the user creates a new session?
I was thinking about having a table structured like this:
RowID int (unique)
When the user logins I insert a record with the User ID and login time. When the user logs out I update the logout time using the RowID. I am hoping the add the SPID as an additional point of information.