Post reply

Auditing, differentiate users

  • January 23, 2014 at 6:41 am

    #287549

    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

    #1682769

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 23, 2014 at 7:45 am

    #1682788

    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

    #1682808

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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