Sql-server – Audit sysadm users

sql server 2014

I have a SQL 2014 Server that has several transactional replicated databases that are used for reporting purposes. Also on that server there is a windows group containing several local local people. This group has sysadm privileges (mainly so that the local people can schedule and edit each others agent jobs).

I need to monitor activity on the replicated databases by the members of this group.

I've tried setting up a database level audit and specifying from the dbo group. This works to a certain extend up has the unfortunate side effect of also picking up all of the replicated transactions as the user running the replication of also a dbo of these databases.

Is there any way of excluding a particular user from an audit? I've tried mapping this group as a user to one of the databases but the audit failed to pick up anything

Best Answer

I found an answer to this:

Create an Audit as disabled Create a Database Audit Specification and link it to your audit. Add INSERT, UPDATE and DELETE and select either the database or required objects or schema. In the principal field select DBO

Use the following statement to alter the Audit

ALTER SERVER AUDIT [Audit Name] WHERE server_principal_name <> N'domain\serviceaccount';

Enable the Audit and Specification

This will now capture all INSERTS/UPDATES/DELETES from the dbo use (sysadm always maps to a database as dbo) excluding the specified service account.

Related Solutions

Sql-server – “A severe error occurred” – Login Failed (but only occasionally)

Just in case this helps, I'm posting some code that might help you work out what account that sid represents:

USE tempdb;
GO
-- to translate SID in binary format to AD format
CREATE FUNCTION [dbo].[fn_SIDToString]
(
  @BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN

  IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)

  DECLARE @StringSID VARCHAR(100)
  DECLARE @i AS INT
  DECLARE @j AS INT

  SELECT @StringSID = 'S-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
  SELECT @StringSID = @StringSID + '-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))

  SET @j = 9
  SET @i = LEN(@BinSID)

  WHILE @j < @i
  BEGIN
    DECLARE @val BINARY(4)
    SELECT @val = SUBSTRING(@BinSID, @j, 4)
    SELECT @StringSID = @StringSID + '-'
      + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, 
            REVERSE(CONVERT(VARBINARY, @val)))))
    SET @j = @j + 4
  END
  RETURN ( @StringSID )
END
GO

SELECT dbo.fn_SIDToString(sp.sid), sp.*
FROM sys.server_principals sp
WHERE dbo.fn_SIDToString(sp.sid) = 'S-1-9-3-3788657366-1166700905-846186909-3339902698';

Thanks goes to @James for pointing out that the sid might be in master.sys.server_principals and to @AaronBertrand for pointing me to a great piece of code that will translate SQL Server binary sids to the Windows format.

N.B.: I put this function in tempdb to avoid creating it in a permanent place. You may want to put it somewhere else for future use, since tempdb gets recreated automatically upon SQL Server startup, and thus the function here will not survive service restarts or machine reboots, etc.

Sql-server – Installing SQL Server 2014 Express fails with error “ Could not find the Database Engine startup handle.”

Could not find database engine startup handle error means SQL Server installation was able to install SQL Server services but when installation is trying to start SQL Server services its failing.

A simple solution I mentioned In This Technet Article is

Please go to SQL Server configuration manager and locate the SQL Server services

Right click on SQL Server express service and select properties and change startup account to Local System and select apply then OK. This will start services with Local system account privilege.

enter image description here

Certain reasons which can cause this error.

The account which user selected on Server Configuration page window ( during installation) is somehow not able to bring SQL Server database engine services online. Either is lacks privilege or it is corrupted. During installation of database engine services SQL Server tries to bring online database services as a internal process but due to startup account either corrupt or not having appropriate privileges it fails to do so and ultimately installation fails.
Other reason is when installation fails first time due to some reason and user uninstall failed installation from add remove program, the un-installation leaves account in broken state so any further attempt to install flashes this error message.
The reason can be also that SQL Server installation was successful in installing SQL Server and its services but due to some other limitation in system or other system related errors SQL Server is not able to come online.

In Your case reason is

2015-02-08 19:55:06.16 spid14s     Error: 17190, Severity: 16, State: 1.
2015-02-08 19:55:06.16 spid14s     Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
2015-02-08 19:55:06.16 spid14s     Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2015-02-08 19:55:06.16 spid14s     Error: 17182, Severity: 16, State: 1.

To solve this Either start SQL Server service account with Local System or Create a new local account having admin privileges on the local machine and try starting SQL Server service with that account. This Blogs.msdn article says such error can come if account profile is corrupt

As you are already aware running SQL Server service with account having admin privileges is not advised. In such case for time being you can run the account with local system but you can use Configure Windows Service Account and Permissions to create account with minimum privileges.

EDIT:

FCB::Open failed: Could not open file E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf for file number 1. OS error: 2(The system cannot find the file specified.).

2015-02-09 10:42:45.12 spid8s Error: 5120, Severity: 16, State: 101.

2015-02-09 10:42:45.12 spid8s Unable to open the physical file "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf". Operating system error 2: "2(The system cannot find the file specified.)".

2015-02-09 10:42:45.12 spid8s Error: 17207, Severity: 16, State: 1.

As per below logs SQL Server is searching for msdb file at location E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\

Can you manually move both msdb data file and log file to above location. I know this is not your desired location but let first SQL Server come online then we can use alter database command and change location to what ever suits you. To me this is bug and is happening because SQL Server is unable to ,somehow, decide the location of msd files and so choosing random location.

Revert if this does not works

Best Answer

Related Solutions

Sql-server – “A severe error occurred” – Login Failed (but only occasionally)

Sql-server – Installing SQL Server 2014 Express fails with error “ Could not find the Database Engine startup handle.”

Related Question