SQL Server Profiler

As part of Auditing and Logging run Server side trace as below :

As a best practice it is best to perform server side traces whenever possible.  Also, trace data should be stored in a location other than the location of any database files.  This trace data needs to be maintained, so that a drive volume does not fill up.  Every instance will have a sample baseline fifteen minute Profiler trace captured when built. A Profiler trace will also be captured after the installation and upgrade of each database. A Profiler trace will also be captured monthly. Use the following events for the trace:

Audit Add DB User Event                      Occurs when a database user login has been added or removed.

Audit Add Login to Server Role              Addition or removal of login accounts to/from server roles.

Audit Add Member to DB Role               Addition and deletion of logins from a database role.

Audit Add Role Event                            Occurs when a database role is added or removed.

Audit Addlogin                                      Event Occurs when a login has been added or removed.

Audit App Role                                     Change Password Whenever passwords are changed for an                                                                   application role.

Audit Backup/Restore                            Occurs whenever a backup or restore command is issued.

Audit Broker Conversation                     Reports audit messages related to Service Broker dialog security.

Audit Broker Login                                Reports audit messages related to Service Broker transport                                                                    security.

Audit Change Audit                               Occurs whenever an audit trace modification is made.

Audit Change Database Owner               Occurs when you use the ALTER AUTHORIZATION statement to                                                  change the owner of a database, and the permissions required to                                                           do that are checked.

Audit DBCC                                          Occurs whenever a DBCC command is issued 

Audit Database Management                 Occurs when a database is created, altered, or dropped.

Audit Database Object Access              Occurs when database objects, such as schemas, are accessed.

Audit Database Object GDR                  Occurs when a GRANT, REVOKE, or DENY has been issued for                                                             database objects, such as assemblies and schemas.

Audit Database Object Management      Occurs when a CREATE, ALTER, or DROP statement is executed                                                           on database objects, such as schemas.

Audit Database Object Take Ownership  Occurs when a change of owner for objects within database scope                                                         occurs.

Audit Database Operation                      Occurs when operations in a database, such as checkpoint or                                                                 subscribe query notification, occur.

Audit Database Principal Impersonation Occurs when an impersonation occurs within the database scope,                                                           such as EXECUTE AS <user> or SETUSER.

 Audit Database Principal Management  Occurs when principals, such as users, are created, altered, or                                                                dropped from a database.

Audit Database Scope GDR                  Occurs whenever a GRANT, REVOKE, or DENY is issued for a                                                               statement permission by any user in Microsoft SQL Server for                                                                database-only actions such as granting permissions on a database.

 Audit Login Change Password              Occurs whenever a user changes their Microsoft SQL Server login                                                           password.

 Audit Login Change Property                Occurs when you use the sp_defaultdb stored procedure, the                                                                 sp_defaultlanguage stored procedure, or the ALTER LOGIN                                                                    statement to modify a property of a login.

Audit Login                                           Occurs when a user has successfully logged in to SQL Server.

Audit Login Failed                                 Indicates that a user tried to log in to Microsoft SQL Server and                                                              failed.

Audit Login GDR                                   Event Occurs when a Microsoft Windows login right was added or                                                          removed.

Audit Logout                                         Indicates that a user has logged out of (logged off) Microsoft SQL                                                         Server.

Audit Object Derived Permission Event  Occurs when a CREATE, ALTER, or DROP was issued for an                                                                 object.

Audit Schema Object Access                 Occurs when an object permission (such as SELECT) is used.

Audit Schema Object GDR                    Occurs whenever a GRANT, REVOKE, or DENY is issued for a                                                               schema object permission by any user in Microsoft SQL Server.

Audit Schema Object Management         Occurs when server objects are created, altered, or dropped.

Audit Schema Object Take Ownership    Occurs when the permissions to change the owner of schema                                                                 object (such as a table, procedure, or function) is checked. This                                                             happens when the ALTER AUTHORIZATION statement is used to                                                            assign an owner to an object.

Audit Server Alter Trace                         Occurs for all statements that check for the ALTER TRACE                                                                     permission. Statements that check for ALTER TRACE include                                                                 those used to create or configure a trace, or to set a filter on a                                                               trace.

 Audit Server Object GDR                      Occurs whenever a GRANT, REVOKE, or DENY is issued for a                                                               server object permission by any user in Microsoft SQL Server.

 Audit Server Object Management          Occurs in the case of CREATE, ALTER, or DROP for server                                                                   objects.

Audit Server Object Take Ownership       Occurs when the owner is changed for objects in server scope.

Audit Server Operation                          Occurs when Security Audit operations such as altering settings,                                                                         resources, external access, or authorization are used.

Audit Server Principal Impersonation      Occurs when there is an impersonation within server scope, such                                                            as EXECUTE AS <login>.

Audit Server Principal Management        Occurs when server principals are created, altered, or dropped.

Audit Server Scope GDR                       Occurs when a GRANT, REVOKE, or DENY is issued for                                                                        permissions in the server scope, such as creating a login.

Audit Server Starts and Stops                Occurs when the Microsoft SQL Server service state is modified.

Audit Statement Permission Event         Occurs when a statement permission has been used.