MS SQL 2014 get SPIDs of users
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:
USERID char(3)
LoginTime datetime
LogoutTime datetime
RowID int (unique)
SPID int
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.
I was thinking about having a table structured like this:
USERID char(3)
LoginTime datetime
LogoutTime datetime
RowID int (unique)
SPID int
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well as far as I'm aware there are no "LOGOUT" SQL Servers triggers but for the LOGON audit you could use a trigger like below:
CREATE TABLE LogonAudit
(
AuditID INT NOT NULL CONSTRAINT PK_LogonAudit_AuditID
PRIMARY KEY CLUSTERED IDENTITY(1,1)
, UserName NVARCHAR(255)
, LogonDate DATETIME
, spid INT NOT NULL
, ClientIP SYSNAME
);
GO
GRANT INSERT ON dbo.LogonAudit TO public;
GO
CREATE TRIGGER LogonAuditTrigger ON ALL SERVER FOR LOGON
AS
BEGIN
--IF SUSER_SNAME() <> 'sa'
INSERT INTO dbo.LogonAudit (UserName, LogonDate, spid, ClientIP)
VALUES (SUSER_SNAME(), GETDATE(), @@SPID, (SELECT client_net_address as ipaddess FROM sys.dm_exec_connections WHERE session_id=@@SPID) );
END;
GO
ENABLE TRIGGER LogonAuditTrigger ON ALL SERVER;
CREATE TABLE LogonAudit
(
AuditID INT NOT NULL CONSTRAINT PK_LogonAudit_AuditID
PRIMARY KEY CLUSTERED IDENTITY(1,1)
, UserName NVARCHAR(255)
, LogonDate DATETIME
, spid INT NOT NULL
, ClientIP SYSNAME
);
GO
GRANT INSERT ON dbo.LogonAudit TO public;
GO
CREATE TRIGGER LogonAuditTrigger ON ALL SERVER FOR LOGON
AS
BEGIN
--IF SUSER_SNAME() <> 'sa'
INSERT INTO dbo.LogonAudit (UserName, LogonDate, spid, ClientIP)
VALUES (SUSER_SNAME(), GETDATE(), @@SPID, (SELECT client_net_address as ipaddess FROM sys.dm_exec_connections WHERE session_id=@@SPID) );
END;
GO
ENABLE TRIGGER LogonAuditTrigger ON ALL SERVER;
ASKER
Well since they are logging out via my application I could create a logout routine in there. If they are foolish enough to not log out at night I will know that and I can go pound on their heads. This should be enough to get me started.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The triggers did help but not for this application. Behind the scenes, the embedded connect string uses 'sa' for everyone. My application passes it own user ID so writing to a table made more sense.
ASKER