January 23, 2014 at 6:41 am
Hi All,
I have a problem about auditing in SQL Server 2012. Before searching thru Internet, I would like to get some information in order to avoid wasting my time by looking something not useful for me.
Ok, this is what i need to do ..
I want to setup an audit mechanism. I have a user who has sysadmin role. Lets say sqladmin. In addition to that i have users which starts with DBA_***. As you guess dba users also have sysadmin roles. Dba users and sqladmin can select, insert,update,delete on tables due to the fact that they login on tables as dbo since they have sysadmin role.
My problem starts here. Because i want to setup an audit mechanism that should audit only dba users not sqladmin user. ( I can seperate any users anyways since they do not login as dbo ). So, Is there any way to differentiate dba users from sqladmin user ?
Any Suggestions ?
Thanks in advance
January 23, 2014 at 7:22 am
If the users are coming in under different accounts, isn't the account the differentiator? I realize the roles are the same, but that's a login mapped to a role (hopefully) and you should be capturing the queries based on the user, not the role.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 23, 2014 at 7:45 am
Hi,
Yes they come in under different accounts and it is easy to differentiate if you do SERVER AUDIT since they have different login accounts under instance. However, it is not easy if you have only dbo user under table if you do TABLE AUDIT.
January 23, 2014 at 8:08 am
Oh, if they're mapped to a single user, yeah, you're kind of stuck. There's no easy way there. You'd have to modify your structure so that instead of mapping them to the user in the database, you map them to the role, same as you do at the server level.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply