Hit on DB from a User

Question

Hit on DB from a User

VastSQL

SSC-Dedicated

Points: 36067
More actions
December 5, 2013 at 6:45 am

#287101

Hi Experts,
We have a DB user names Supply . Application will hit the DB with this username.
Is there anyway to find the hit happening to the database?\
Thanks in Advance.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

Ratheesh.K.Nair (12/5/2013)
Hi Experts,
We have a DB user names Supply . Application will hit the DB with this username.
Is there anyway to find the hit happening to the database?Thanks in Advance.

hit? not a SQL term, but here's some examples:

a connection can be found that has the username connecting via sp_who/sp_who2/sp_whoisactive or from a query like this:

select * from master.sys.dm_exec_sessions WHERE login_name = 'myDomain\lowell'

that's at the SERVER level, though;

at the database level, no one "logs in" or anything, but they might select/insert/update, so to track that you'd have to trace/audit etc to get info on who-did-what at the database level.

what exactly are you trying to do?

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!

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 2

I've been asked before how many times a particular user has accessed objects in a database.

I solved it by creating a database audit specification to capture the SCHEMA_OBJECT_ACCESS_GROUP.

Here's the code I used

-- Setup the auditing

USE [master]

GO

CREATE SERVER AUDIT [User-Access]

TO FILE

(FILEPATH = N'F:\SQLdata\MSSQL10_50.A\MSSQL\Log' -- REPLACE WITH YOUR LOG PATH

,MAXSIZE = 0 MB

,MAX_ROLLOVER_FILES = 2147483647

,RESERVE_DISK_SPACE = OFF

)

WITH

(QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

)

GO

ALTER SERVER AUDIT [User-Access] WITH (STATE = ON)

GO

USE [YOUR-DATABASE]

GO

CREATE DATABASE AUDIT SPECIFICATION [User-Access]

FOR SERVER AUDIT [User-Access]

ADD (SCHEMA_OBJECT_ACCESS_GROUP)

GO

ALTER DATABASE AUDIT SPECIFICATION [User-Access] WITH (STATE = ON)

GO

-- Let it run for a bit

-- Now stop the capture

USE [YOUR-DATABASE];

GO

ALTER DATABASE AUDIT SPECIFICATION [User-Access] WITH (STATE = OFF)

USE master;

GO

ALTER SERVER AUDIT [User-Access] WITH (STATE = OFF)

GO

-- Get the results

SELECT schema_name, object_name, server_principal_name, count(*)

FROM fn_get_audit_file('F:\SQLdata\MSSQL10_50.A\MSSQL\Log\User-Access*', default, default) -- REPLACE WITH YOUR LOG PATH

WHERE server_principal_name = 'THE-LOGIN-YOU-ARE-INTERESTED-IN'

AND schema_name NOT IN ('sys')

GROUP BY schema_name, object_name, server_principal_name

ORDER BY schema_name, object_name, server_principal_name

GO

Hope this helps.

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com