Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Hit on DB from a User Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 6:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 10:44 AM
Points: 2,634, Visits: 3,985
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.
Post #1520032
Posted Thursday, December 5, 2013 7:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 12,927, Visits: 32,330
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1520063
Posted Thursday, December 5, 2013 9:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:05 AM
Points: 283, Visits: 1,119
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
Post #1520164
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse