Audit of sysadmin members RRS feed

  • Question

  • A Sql Server 2012 Database Audit performs an audit on sysadmins when you specify the principal DBO. However, this causes ALL the sysadmins to be audited. What configuration or solution can be used to audit ONLY 1 specific group that has sysadmin membership without auditing all the activity by ALL sysadmins? We are trying to cut down on action events that are not necessary (i.e. sql engine service account or other service accounts that are sysadmin and do not need audited). This is a user database. 
    Monday, April 3, 2017 3:58 PM

All replies

  • When I read the topic for CREATE DATABASE AUDIT SPECIFICATION in Books Online, it is not clear to me whether you specify a database or a server principal. They say "SQL Server principal", which certainly sounds like a server principal.

    If so, you could put all sysadmins to be audited in a server role and off you do.

    But when I use the UI in SSMS, it more seems like it is a database principals, and in such case you are a little out of luck, since if you are sysadmin you map to dbo in all databases. The alternative is to remove people from sysadmin and grant them CONTROL SERVER which is almost the same. But with CONTROL SERVER you map to your own name in user database. Alas, there are actions in SQL Server where CONTROL SERVER is not enough, but where you must be sysadmin, so it is not really a tenable option.

    Monday, April 3, 2017 8:11 PM
  • Yes, that should say database principal. I'm fixing that.

    So Erland is correct. A database audit can only track database principals. All sysadmins, enter the database as the DBO user, so you can't tell them apart by user name. Your basic plan of subcategorizing sysadmin's is going to be difficult. I endorse Erlands idea of using CONTROL SERVER (or something less) instead of sysadmin.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, April 4, 2017 3:57 PM
  • Thank you for the reply. Looks like I've hit a roadblock with SQL Server auditing. Control server has been tested and is not sufficient for DBA's to administer the servers. They would not be able to change job steps, run various DBCC commands and install software that requires sysadmin during install and various other tasks that are only available to sysadmin. Besides, changing an organizations server security access model should not be a work around for a deficiency in a SQL Server feature like auditing. 

    What should happen is the principle (i.e. user or group) should be audited consistently regardless of sysadmin membership. To put it another way, DBO should not be a principal requirement to audit a user/group that has been added to the sysadmin role. 

    Thanks again.

    Tuesday, April 4, 2017 4:21 PM
  • What should happen is the principle (i.e. user or group) should be audited consistently regardless of sysadmin membership. To put it another way, DBO should not be a principal requirement to audit a user/group that has been added to the sysadmin role. 

    The principal is is audited consistently. Keep in mind that this is on database level, and in the database all sysadmin are dbo. That is certainly impractical, but it has been way since SQL 6.0 - there is a lot of legacy in this product.

    The solution that come closest at hand is that you are permitted to filter by more items, for instance a server principal. Then you would be able to filter on a server role. (Well, maybe. I don't know if you can filter on a database role today.)

    I would suggest that you submit a suggestion (not bug) on http://connect.microsoft.com/SqlServer/Feedback
    Be careful to state the business value in the change - that matters a lot for Microsoft.

    Tuesday, April 4, 2017 9:36 PM
  • Ok, will leave feedback. Just to be clear. The database audit principal is NOT consistent. If a database audit is setup and the principal is set to a windows AD group that is added as a login on the sql server there are issues. The audit will stop logging once you add that group as a member to sysadmin. This is NOT consistency. A database audit should never stop auditing based on server role membership. This is a gotcha that is very serious. Any other justifications of this behavior would just be using semantics as an excuse. 
    Wednesday, April 5, 2017 3:44 PM
  • While the Windows AD group is added as a server login, this does not really matter. It still handled entirely on database level. That is, is database user part of the AD group? Yes, it is, we will audit. Once the user is mapped to sysadmin, the user becomes dbo which is not a member of any AD group.

    You can see this by logging in as a non-sysadmin user and run

    SELECT * FROM sys.user_token
    in a plain database. I would expect that you will all Windows groups the user is a member of, regardless whether they are server logins or not.

    Then login as sysadmin user. You will see a single line of dbo. The knoweledge about the Windows groups are gone. And they have to be gone, as user tokens affect more than just auditing, but things like permissions.
    You may not like this, and it may not be optimal. However, you need to understand that SQL Server was not born in its current shape with version you are using, but there is a long history of legacy.

    Wednesday, April 5, 2017 9:29 PM