Permissions Required to Run SQL Server Profiler

  • Article

By default, running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures that are used to create traces. To run SQL Server Profiler, users must be granted the ALTER TRACE permission. For more information, see GRANT Server Permissions (Transact-SQL).

Permissions Used to Replay Traces

Replaying traces also requires that the user who is replaying the trace have the ALTER TRACE permission.

However, during replay, SQL Server Profiler uses the EXECUTE AS command if an Audit Login event is encountered in the trace that is being replayed. SQL Server Profiler uses the EXECUTE AS command to impersonate the user who is associated with the login event.

If SQL Server Profiler encounters a login event in a trace that is being replayed, the following permission checks are performed:

  1. User1, who has the ALTER TRACE permission, starts replaying a trace.
  2. A login event for User2 is encountered in the replayed trace.
  3. SQL Server Profiler uses the EXECUTE AS command to impersonate User2.
  4. SQL Server attempts to authenticate User2, and depending on the results, one of the following occurs:
    1. If User2 cannot be authenticated, SQL Server Profiler returns an error, and continues replaying the trace as User1.
    2. If User2 is successfully authenticated, replaying the trace as User2 continues.
  5. Permissions for User2 are checked on the target database, and depending on the results, one of the following occurs:
    1. If User2 has permissions on the target database, impersonation has succeeded, and the trace is replayed as User2.
    2. If User2 does not have permissions on the target database, the server checks for a Guest user on that database.
  6. Existence of a Guest user is checked on the target database, and depending on the results, one of the following occurs:
    1. If a Guest account exists, the trace is replayed as the Guest account.
    2. If no Guest account exists on the target database, an error is returned and the trace is replayed as User1.

The following diagram shows this process of checking permission when replaying traces:

SQL Server Profiler replay trace permissions

See Also

Tasks

How to: Create a Trace (SQL Server Profiler)
How to: Replay a Trace Table (SQL Server Profiler)
How to: Replay a Trace File (SQL Server Profiler)

Concepts

Replaying Traces

Other Resources

SQL Server Profiler Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance