Options for auditing SQL logins

Question

Options for auditing SQL logins

DataAnalyst011

SSCarpal Tunnel

Points: 4035
More actions
December 15, 2016 at 1:15 pm

#332822

We're running SQL 2014 SP1. We're needing to audit who is logging into SQL to check if SQL users are still being used. I see a couple of ways to do it. We could use...
Databases > Security > Audits > SUCCESSFUL_LOGIN_GROUP
or right-click SQL instance > Properties > Security > Successful Logins Only (or more likely Both Failed and Successful Logins)
Any advice on how best to do this? I've been researching both of these methods but I'm not sure what the best way to go. I fully confess I'm a rookie, so I apologize if I'm missing the obvious! Thanks in advance.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Audit has certainly it's purpose, but be aware it can generate a lot of data and it could be hard to filter out exactly what you need.

If you only need to know the last time a login is used I guess using a logon trigger is a better option. Create a table with a column for "login name" and one for "last used". Store all the login names in the table and use the trigger update the "lasst used" column of the row with that login name.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Audit works well, and you can keep the data, but it's hard to parse. It's more like it was built to track things for a human to review, which is hard when you have a large amount of data.

I'd second the logon trigger, but test extensively and be careful. Make sure you know how to disable it as well if something breaks.

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 3

DataAnalyst011

SSCarpal Tunnel

Points: 4035

December 16, 2016 at 7:16 am

#1918386

Thank you both very much! That really helps!

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 4

Here is what I tried in our test environment, but I am getting this error:

Login failed for user 'testuser'. (Microsoft SQL Server, Error: 18456)

(I was able to get back in with this: http://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/)

Below is my DDL. I'm sure I'm missing something obvious, but after making a few modifications I cannot figure it out. Any suggestions?

CREATE TABLE UserLogon

(

UserName NVARCHAR(255),

LastLogon DATETIME

)

INSERT INTO UserLogon (UserName)

VALUES

('testuser')

GO

GRANT INSERT ON Test.dbo.UserLogon TO public;

GO

CREATE TRIGGER LogonTrigger ON ALL SERVER FOR LOGON

AS

BEGIN

UPDATE UserLogon

SET UserName = SUSER_SNAME(),

LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME()

END;

GO

ENABLE TRIGGER LogonTrigger ON ALL SERVER;

HanShi SSC-Dedicated Points: 33507 More actions · Answer 5

I'm not able to test because I don't have a computer at hand.

But is the 'testuser' account added as a user account to the test database?

Second I suggest to use the three name object naming in the update statement. Remember a login can have a different default database or use a different database in the connection string.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Lowell SSC Guru Points: 323518 More actions · Answer 6

i always put my audit table in the master database, since it is guaranteed to exist, and all logins inherit some access to it.

someone already mentioned the three part insert: that's mandatory without database context,and a login might not have a default database of [test] in your example.

shouldn't your trigger be doing an upsert? insert if not exists/update otherwise?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 7

Ha, I've never heard the term "upsert". That's brilliant 🙂

I have a meeting, but then I will take these latest suggestions and test. Thanks again to all for the help.

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 8

The three part naming convention did the trick. Thanks again.

I took the suggestion to insert if the username doesn't exist and update if it does. I found out quickly why this is needed. If the user isn't in my UserLogon table they are prevented from logging in! I still can't quite get it right though. I created a new SQL login with a username that wasn't in my table. However, I still receive the error "Logon failed for login 'testuser2' due to trigger execution". Below is my full code. Would you mind helping figure out what I'm doing wrong?

GRANT INSERT ON master.dbo.UserLogon TO public;

GO

CREATE TRIGGER LogonTrigger ON ALL SERVER FOR LOGON

AS

IF (NOT EXISTS(SELECT UserName FROM master.dbo.UserLogon WHERE UserName = SUSER_SNAME()))

BEGIN

INSERT INTO master.dbo.UserLogon (UserName, LastLogon)

SELECT SUSER_SNAME(), GETDATE()

END

ELSE

BEGIN

UPDATE master.dbo.UserLogon

SET UserName = SUSER_SNAME(),

LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME()

END;

GO

ENABLE TRIGGER LogonTrigger ON ALL SERVER;

Lowell SSC Guru Points: 323518 More actions · Answer 9

i would handle the upsert like this:

also, i think you granted INSERT to public, but you did not grant UPDATE or SELECT to public, so that is a point of failure too!

you can see that i'm testing rows affected to see if anything was inserted

also, we don't want to update the suser_name to itself in the update, just the new date.

DECLARE @rows int

INSERT INTO master.dbo.UserLogon (UserName, LastLogon)

SELECT SUSER_SNAME(), GETDATE()

WHERE NOT EXISTS(SELECT UserName FROM master.dbo.UserLogon WHERE UserName = SUSER_SNAME())

SET @rows = @@ROWCOUNT

IF @rows= 0

UPDATE master.dbo.UserLogon

SET LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME()

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 10

DataAnalyst011

SSCarpal Tunnel

Points: 4035

December 16, 2016 at 3:11 pm

#1918511

Excellent. Thank you very much.

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 11

Do you mind if I ask one more question? Obviously your code works and mine didn't. However, do you use the @@ROWCOUNT method because it is faster? I've pretty new to all of this, and haven't seen that used before. I appears to be keeping count of the rows produced by the query. If there are no rows by the IF then the UPDATE is executed. Am I following you correctly?

Lowell SSC Guru Points: 323518 More actions · Answer 12

DataAnalyst011 (12/16/2016)
Do you mind if I ask one more question? Obviously your code works and mine didn't. However, do you use the @@ROWCOUNT method because it is faster? I've pretty new to all of this, and haven't seen that used before. I appears to be keeping count of the rows produced by the query. If there are no rows by the IF then the UPDATE is executed. Am I following you correctly?

@@rowcount returns the number of rows affected from the previous command in a session; so you have to be careful, because it's per command...BEGIN or DECLARE @variable counts as a command, and would return zero rows affected , so you have to make sure you get the value immediately after a command that touches rows of data.

so I can't say it's faster, it's really just a style of coding I think looks nice.

your IF..ELSE would work just fine, with the minor adjustments to permissions we mentioned before;

there's always more than one way to tackle the same problem, so it's just a matter of sticking with a style you like.

this is perfectly workable as well:

CREATE TRIGGER LogonTrigger ON ALL SERVER FOR LOGON

AS

IF (NOT EXISTS(SELECT UserName FROM MASTER.dbo.UserLogon WHERE UserName = SUSER_SNAME()))

BEGIN

INSERT INTO MASTER.dbo.UserLogon (UserName, LastLogon)

SELECT SUSER_SNAME(), GETDATE()

END

ELSE

BEGIN

UPDATE MASTER.dbo.UserLogon

SET LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME()

END;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 13

Thank you again very much for the helpful explanations! I appreciate you taking the time.

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 14

I hope you won't mind me asking for a little more help on this. I've been testing this script and things were going well until this morning. I have around a dozen SQL Reporting Services subscriptions that all fire at the same, and it appears to have caused a deadlock for my logon trigger process (which in turn prevented that SQL user from logging in a few times). I'm looking through the deadlock graph, but I'm not sure what I could do to resolve it. I'm including the deadlock graph. Any help is much appreciated!

<deadlock-list>

<deadlock victim="process1c77f0508">

<process-list>

<executionStack>

<frame procname="1563152614" line="10" stmtstart="654" stmtend="828" sqlhandle="0x03000100e6d02b5dbfead400f6a600000000000000000000">

UPDATE master.dbo.userlogon

SET LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME() </frame>

</executionStack>

<inputbuf>

Proc [Database Id = 10 Object Id = 1063674837] </inputbuf>

</process>

<executionStack>

<frame procname="1563152614" line="10" stmtstart="654" stmtend="828" sqlhandle="0x03000100e6d02b5dbfead400f6a600000000000000000000">

UPDATE master.dbo.userlogon

SET LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME() </frame>

</executionStack>

<inputbuf>

Proc [Database Id = 10 Object Id = 311672158] </inputbuf>

</process>

</process-list>

<resource-list>

<ridlock fileid="1" pageid="273" dbid="1" objectname="master.dbo.userlogon" id="lock1bd079500" mode="U" associatedObjectId="72057594038976512">

<owner-list>

<owner id="process1f1820bc8" mode="U"/>

</owner-list>

<waiter-list>

<waiter id="process1c77f0508" mode="U" requestType="convert"/>

</waiter-list>

</ridlock>

<ridlock fileid="1" pageid="273" dbid="1" objectname="master.dbo.userlogon" id="lock1bd079500" mode="U" associatedObjectId="72057594038976512">

<owner-list>

<owner id="process1c77f0508" mode="S"/>

</owner-list>

<waiter-list>

<waiter id="process1f1820bc8" mode="X" requestType="convert"/>

</waiter-list>

</ridlock>

</resource-list>

</deadlock>

<deadlock victim="process3fb94c8">

<process-list>

<executionStack>

<frame procname="1563152614" line="10" stmtstart="654" stmtend="828" sqlhandle="0x03000100e6d02b5dbfead400f6a600000000000000000000">

UPDATE master.dbo.userlogon

SET LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME() </frame>

</executionStack>

<inputbuf>

Proc [Database Id = 10 Object Id = 1063674837] </inputbuf>

</process>

<executionStack>

<frame procname="1563152614" line="10" stmtstart="654" stmtend="828" sqlhandle="0x03000100e6d02b5dbfead400f6a600000000000000000000">

UPDATE master.dbo.userlogon

SET LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME() </frame>

</executionStack>

<inputbuf>

Proc [Database Id = 10 Object Id = 311672158] </inputbuf>

</process>

<executionStack>

<frame procname="1563152614" line="10" stmtstart="654" stmtend="828" sqlhandle="0x03000100e6d02b5dbfead400f6a600000000000000000000">

UPDATE master.dbo.userlogon

SET LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME() </frame>

</executionStack>

<inputbuf>

(@SnapshotId uniqueidentifier,@IsPermanent bit,@ChunkName nvarchar(36),@ChunkType int,@ChunkId uniqueidentifier output,@ChunkFlags tinyint output,@MimeType nvarchar(260) output)

if (@IsPermanent = 1) begin

select@ChunkId = ChunkId,

@ChunkFlags = ChunkFlags,

@MimeType = MimeType

from dbo.SegmentedChunk chunk

where chunk.SnapshotDataId = @SnapshotId and chunk.ChunkName = @ChunkName and chunk.ChunkType = @ChunkType

selectcsm.SegmentId,

csm.LogicalByteCount as LogicalSegmentLength,

csm.ActualByteCount as ActualSegmentLength

from ChunkSegmentMapping csm

where csm.ChunkId = @ChunkId

order by csm.StartByte asc

end

else begin

select@ChunkId = ChunkId,

@ChunkFlags = ChunkFlags,

@MimeType = MimeType

from [DWReportingTempDB].dbo.SegmentedChunk chunk

where chunk.SnapshotDataId = @SnapshotId and chunk.ChunkName = @ChunkName and chunk.ChunkType = @ChunkType

if @ChunkFlags & 0x4 > 0 begin

-- Shallow copy: read chunk segments from cat </inputbuf>

</process>

</process-list>

<resource-list>

<ridlock fileid="1" pageid="273" dbid="1" objectname="master.dbo.userlogon" id="lock1bd079500" mode="U" associatedObjectId="72057594038976512">

<owner-list>

<owner id="process2cee08" mode="U"/>

</owner-list>

<waiter-list>

<waiter id="process3fb94c8" mode="U" requestType="convert"/>

</waiter-list>

</ridlock>

<ridlock fileid="1" pageid="273" dbid="1" objectname="master.dbo.userlogon" id="lock1bd079500" mode="U" associatedObjectId="72057594038976512">

<owner-list>

<owner id="process2cee08" mode="U"/>

</owner-list>

<waiter-list>

<waiter id="process3fb9948" mode="U" requestType="convert"/>

</waiter-list>

</ridlock>

<ridlock fileid="1" pageid="273" dbid="1" objectname="master.dbo.userlogon" id="lock1bd079500" mode="U" associatedObjectId="72057594038976512">

<owner-list>

<owner id="process3fb9948" mode="S"/>

</owner-list>

<waiter-list>

<waiter id="process2cee08" mode="X" requestType="convert"/>

</waiter-list>

</ridlock>

</resource-list>

</deadlock>

<deadlock victim="process3fb9948">

<process-list>

<executionStack>

<frame procname="1563152614" line="10" stmtstart="654" stmtend="828" sqlhandle="0x03000100e6d02b5dbfead400f6a600000000000000000000">

UPDATE master.dbo.userlogon

SET LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME() </frame>

</executionStack>

<inputbuf>

Proc [Database Id = 10 Object Id = 1063674837] </inputbuf>

</process>

<executionStack>

<frame procname="1563152614" line="10" stmtstart="654" stmtend="828" sqlhandle="0x03000100e6d02b5dbfead400f6a600000000000000000000">

UPDATE master.dbo.userlogon

SET LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME() </frame>

</executionStack>

<inputbuf>

Proc [Database Id = 10 Object Id = 311672158] </inputbuf>

</process>

<executionStack>

<frame procname="1563152614" line="10" stmtstart="654" stmtend="828" sqlhandle="0x03000100e6d02b5dbfead400f6a600000000000000000000">

UPDATE master.dbo.userlogon

SET LastLogon = GETDATE()

WHERE UserName = SUSER_SNAME() </frame>

</executionStack>

<inputbuf>

(@SnapshotId uniqueidentifier,@IsPermanent bit,@ChunkName nvarchar(36),@ChunkType int,@ChunkId uniqueidentifier output,@ChunkFlags tinyint output,@MimeType nvarchar(260) output)

if (@IsPermanent = 1) begin

select@ChunkId = ChunkId,

@ChunkFlags = ChunkFlags,

@MimeType = MimeType

from dbo.SegmentedChunk chunk

where chunk.SnapshotDataId = @SnapshotId and chunk.ChunkName = @ChunkName and chunk.ChunkType = @ChunkType

selectcsm.SegmentId,

csm.LogicalByteCount as LogicalSegmentLength,

csm.ActualByteCount as ActualSegmentLength

from ChunkSegmentMapping csm

where csm.ChunkId = @ChunkId

order by csm.StartByte asc

end

else begin

select@ChunkId = ChunkId,

@ChunkFlags = ChunkFlags,

@MimeType = MimeType

from [DWReportingTempDB].dbo.SegmentedChunk chunk

where chunk.SnapshotDataId = @SnapshotId and chunk.ChunkName = @ChunkName and chunk.ChunkType = @ChunkType

if @ChunkFlags & 0x4 > 0 begin

-- Shallow copy: read chunk segments from cat </inputbuf>

</process>

</process-list>

<resource-list>

<ridlock fileid="1" pageid="273" dbid="1" objectname="master.dbo.userlogon" id="lock1bd079500" mode="U" associatedObjectId="72057594038976512">

<owner-list>

<owner id="process2cee08" mode="U"/>

</owner-list>

<waiter-list>

<waiter id="process3fb94c8" mode="U" requestType="convert"/>

</waiter-list>

</ridlock>

<ridlock fileid="1" pageid="273" dbid="1" objectname="master.dbo.userlogon" id="lock1bd079500" mode="U" associatedObjectId="72057594038976512">

<owner-list>

<owner id="process2cee08" mode="U"/>

</owner-list>

<waiter-list>

<waiter id="process3fb9948" mode="U" requestType="convert"/>

</waiter-list>

</ridlock>

<ridlock fileid="1" pageid="273" dbid="1" objectname="master.dbo.userlogon" id="lock1bd079500" mode="U" associatedObjectId="72057594038976512">

<owner-list>

<owner id="process3fb9948" mode="S"/>

</owner-list>

<waiter-list>

<waiter id="process2cee08" mode="X" requestType="convert"/>

</waiter-list>

</ridlock>

</resource-list>

</deadlock>

</deadlock-list>