Solved

MS SQL 2014 get SPIDs of users

Posted on 2016-11-04
6
40 Views
Last Modified: 2016-11-22
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.
0
Comment
Question by:rwheeler23
  • 3
  • 3
6 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 41874285
You could use SQL own functionality rather than write your own like described here:
https://mssqltalks.wordpress.com/2013/02/25/how-to-audit-login-to-my-sql-server-both-failed-and-successful/
"Using SQL Server management Studio: Connect instance in Management Studio → Right Click and select  server Properties → Security → Under Login Auditing select both failed and Successful logins and click OK.  This will audit all the login attempts in Error log."

OR "SQL Server – Login Auditing using Logon Triggers" like described here:
https://sqlandme.com/2011/07/13/sql-server-login-auditing-using-logon-triggers/
0
 

Author Comment

by:rwheeler23
ID: 41874308
That is one way to do that however our application logs into SQL as the same user so SQL would see the same user ID.  We pass down a User ID that our application uses so each inserted record will be assigned that value as the record is inserted. That is why I was looking to do this myself.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41874372
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;
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:rwheeler23
ID: 41874516
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.
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 500 total points
ID: 41897999
Did the trigger posted in previous comment helped you at all with the Audit process?
0
 

Author Closing Comment

by:rwheeler23
ID: 41898345
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question