2

I need to setup a sql audit to capture all queries running on a server from any unauthorized locations, and one of the columns that is required is Row Count.

In sql trace, this is possible. But I can't seem to find corresponding column in extended events. I looked into these 2 DMVs:

select * from sys.dm_xe_object_columns
where object_name = 'sql_statement_completed'

select pkg.name as PackageName, obj.name as ActionName
from sys.dm_xe_packages pkg
inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
where obj.object_type = 'action'
order by 1, 2 

But all they have is reads/writes, but not actual number of rows.

Is it possible to get this info in SQL 2008 or SQL 2008 R2 ?

0

That's a bit insane to capture all running queries no? Queries from a specific user or including the ones from sproc, ETL, etc? If they are coming from an unauthorized location, that tells me that your DB is not locked down enough. If one instance has 20 DBs, each of the service accounts, domain users or even SQL login should only been given access to the database pertain to them. The people who are the owner of these accounts (who knows the password) are usually gone trusted and have gone through background checks.

Otherwise, they are 3rd party SQl tools that like idera dm , SQL Sentry and even pefromance dashboard that comes with SSMS 2016 that keep track of queries applied to the database.

  • "from any unauthorized locations" - meaning, there will be a filter that excludes from authorized locations, which is 100% of known existing connections. Could you show a link that performance dashboard tracks all queries.. how to set it up ? – d-_-b Mar 24 '18 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.