CODEWITHSUNDEEP
sql-server-2008-r2audit
Scot Heed
Scot Heed

Reputation: 71

SQL Server 2008 R2 - Audit log - understanding hierarchy

I have searched and not found a decent explanation of the standard SQL2008 audit log output- basics: SQL Server Audit Records.

So first bit.. anyone know of such a link.

I have had to setup an audit on a SQL Server 2008 R2 database to capture execute, insert, update, delete based on the database with the dbo as principle. I have no issues with the setup of auditing. This is returning an expected large amount of data. What is not clear is how to determine the hierarchy in the output. I need to isolate which is the parent object. I was wondering if the 'session id' could be used in conjunction with something else. All sequence no's are 1.

The overall aim is to remove the db access that utilises the dbo and create a role instead. Clearly I want to only assign permissions to the objects that are actually required.

So the main question: Anyone know how to determine the parent object in the audit log?

Thanks folks.

---Extra: I was attempting to determine which objects where called first and thus the level at which the permissions for execution are set. For example when executing a stored proc which then inserts into a table, or executes functions or other stored procs within the audit all the 'actions' are stored from the initial sp exec down to the all the table inserts etc. But the permission is only required on the initial sp not all the other reported objects (ignoring the dynamic sql stuff atm). I was thus hoping to identify the 'top' level so I could assign permissions to a new role. There are a lot of objects in the db and in order to capture the vast majority of permissions the audit has been set on a UAT site which has a reduced user base.

Upvotes: 0

Views: 919

Answers (1)

Randy Franklin Smith
Randy Franklin Smith

Reputation: 11

Not sure what you mean in this case by "hierarchy" or "parent object". There's not really any relationship between audit entries except the sequence dicated by their date/time. Are you trying to determine the table accessed? P.S. I've written a good bit about SQL Audit at www.ultimatewindowssecurity.com/sqlserver.

If I understand it you want to know which tables, views, stored procedures are being accessed by dbo. Is that correct?

Upvotes: 1

Related Questions