Skip to main content

SQL Server Activity Auditing Configuration

In order for the Netwrix Activity Monitor to monitor SQL Server activity, a SQL login with certain server permissions, and must be mapped to user databases.

SQL Database Server Permissions​

  • ALTER ANY EVENT SESSION

    • Allows agent to start or stop an event session or change an event session configuration.

  • VIEW ANY DEFINITION

    • Allows agent to view the SQL Server object definitions.

  • VIEW SERVER STATE

    • Allows agent to access dynamic management views.

Windows Authentication​

Use the following command to create a new login:

create login [DOMAIN\USER] from WINDOWS

Use the following command to grant server permissions:

grant alter any event session to [DOMAIN\USER]
grant view any definition to [DOMAIN\USER]
grant view server state to [DOMAIN\USER]

Use the following command to create a user in each database:

declare @s varchar(max)='';select @s=@s+(case when @s<>'' then char(13)+char(10) else '' end)+'use ['+name+'];create user [DOMAIN\USER] for login [DOMAIN\USER];' from sys.databases;exec(@s)

SQL Authentication​

Use the following command to create a new login:

create login [USER] with password='[PUT_PASSWORD_HERE]'

Use the following command to grant server permissions:

grant alter any event session to [USER]
grant view any definition to [USER]
grant view server state to [USER]

Use the following command to create a user in each database:

declare @s varchar(max)='';select @s=@s+(case when @s<>'' then char(13)+char(10) else '' end)+'use ['+name+'];create user [USER] for login [USER];' from sys.databases;exec(@s)

Logon Trigger (Optional)​

The logon trigger is required to obtain IP Addresses of client connections. Run the following script to allow the Activity Monitor to report client IP Addresses.

CREATE TRIGGER SBAudit_LOGON_Trigger ON ALL SERVER FOR LOGON AS BEGIN declare @str varchar(max)=cast(EVENTDATA() as varchar(max));raiserror(@str,1,1);END