Skip to content

Instantly share code, notes, and snippets.

@Seddryck
Created December 30, 2016 16:02
Show Gist options
  • Save Seddryck/a56d0fed07c8f7aa467f88e8920bd6ca to your computer and use it in GitHub Desktop.
Save Seddryck/a56d0fed07c8f7aa467f88e8920bd6ca to your computer and use it in GitHub Desktop.
Setup extended events about login changes (creation, permissions, roles, ...)
use [master];
go
 
create server audit
    [Audit Login Changes]
to file
(   FILEPATH = N'N:\Trace\'
    , MAXSIZE = 1024 MB
    , MAX_FILES = 10
    , RESERVE_DISK_SPACE = OFF
)
with
(   QUEUE_DELAY = 1000
    , ON_FAILURE = CONTINUE
    , AUDIT_GUID = '33e055a9-1924-47c3-9798-e8875a5bcd42'
);
 
alter server audit
    [Audit Login Changes]
with (state=on);
go
 
create server audit specification
    [Audit Login]
for server audit [Audit Login Changes]
    add (DATABASE_ROLE_MEMBER_CHANGE_GROUP)
    , add (SERVER_ROLE_MEMBER_CHANGE_GROUP)
    , add (DATABASE_PERMISSION_CHANGE_GROUP)
    , add (SERVER_OBJECT_PERMISSION_CHANGE_GROUP)
    , add (SERVER_PERMISSION_CHANGE_GROUP)
    , add (DATABASE_PRINCIPAL_CHANGE_GROUP)
    , add (SERVER_PRINCIPAL_CHANGE_GROUP)
    , add (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
with (state=on);
go
@YasirAnisJanjua
Copy link

Is this extended event or server audit specification ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment