Tracking activity of SQL administrator accounts - audit trail or logging?

Question

Tracking activity of SQL administrator accounts - audit trail or logging?

Viewing 4 posts - 16 through 18 (of 18 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

interesting example;

If I were to modify this trigger, I think I'd like some of the information found in SP_WHO2 like program name and hostname, so you can idnetify who is logging in and who is using sysadmin credentials for their application(noone does that, right? useing 'sa' for their app's login? unpossible!)

when i changed the sql to see some more information, i can find the db they connect to, username, and some other info...but i don't see the spid...how can i get their spid, so I can find info from an sp_who2?

SELECT srm.role_principal_id,*

FROM sys.server_role_members srm

JOIN sys.server_principals sp

ON srm.member_principal_id = sp.principal_id

WHERE role_principal_id = (SELECT principal_id FROM sys.server_principals WHERE NAME = 'sysadmin')

AND ORIGINAL_LOGIN() = sp.NAME

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!

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

Two other DMVs provide the info you're looking for:

- sys.dm_exec_sessions

- sys.dm_exec_connections

K. Brian Kelley
@kbriankelley

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Maxer (8/15/2008)
I have the trace with the column filters on LoginName setup in profiler and had it export it out to script trace definition for MSSQL 05.
Though if the script that starts the trace also specifies the filter parameters then I imagine I'd adjust the settings for that there.
I was also planning on excluding the domain account that SQL Server itself runs under as well as a few of its services since there's no need to trace though. I saw in SQL 2000 you could exclude system accounts it seemed, but I didn't see that option here. (I think it excluded SPIDs for system accounts?)

Yup, build the filter based on the loginame. System related SPIDs are < 50. User SPIDs start at 51. So that's another way to determine whether or not to start a trace.

K. Brian Kelley
@kbriankelley

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

Lowell (8/15/2008)
when i changed the sql to see some more information, i can find the db they connect to, username, and some other info...but i don't see the spid...how can i get their spid, so I can find info from an sp_who2?

Try @@SPID. Failing that, you can pull the info from EVENTDATA.

K. Brian Kelley
@kbriankelley