Permissions required to run SQL Server Profiler

Permissions required to run SQL Server Profiler (performance) *********/

--eg. --Use the Trace account (performancetest) to track SQL Server events.  --Create a Trace login account (performancetest) and grant its ALTER trace and view SERVER State permissions. Use Master CREATE LOGIN performancetest with password= ' [email protected] ';GOGRANT ALTER TRACE to Performancetest;GRANT VIEW SERVER state to Performancetest;GO--Create a Trace user account (Performancetest) in the requirements database customer and grant its Showplan permissions.  Use the Customer GO CREATE USER performancetest from LOGIN performancetest;   GO GRANT SHOWPLAN to Performancetest;  GO /* Description (General): The permissions required for the user to run the profiler are the same as those required to create a trace Transact-SQL stored procedure. Typically, you need to have ALTER TRACE permission to run the profiler. /******************************/PS Note: users with Showplan,alter trace or view SERVER State permissions can view the queries captured in the display schedule output. These queries may contain sensitive information, such as passwords. Therefore-it is recommended that these be granted only one type of information that is authorized to view sensitive information, such as members of the db_owner fixed database role or members of the sysadmin fixed server role; In addition, it is recommended that you save the display plan file or trace file that contains the event that displays the schedule to a location that uses the NTFS file system, and only allow users who are authorized to view sensitive information to access it. */

--Reprint, collection:

Permissions to Replay traces:

Replay tracing also requires the user who replays the trace to have ALTER trace permission. However, if the Audit login event is encountered during replay in the replay trace, the Profiler uses the EXECUTE as command to impersonate the user associated with the logon event. If SQL Server Profiler encounters a logon event in a replay trace, the following permission checks are performed:

1. User A with Altertrace permission begins to replay the trace.

2. A logon event for user B was encountered in the replay trace.

3. SQL Server Profiler uses the EXECUTE as command to impersonate user 2.

4. SQL Server tries to verify the identity of User B, depending on the result, one of the following conditions occurs:

If user B cannot be authenticated, SQL Server Profiler returns an error and continues to replay the trace as user A.

If User B successfully authenticates, the trace continues to be replayed as User B.

5. Check user B's permissions on the target database, depending on the results, one of the following occurs:

If User B has permissions to the target database, the impersonation succeeds and the trace is replayed as User B.

If user B does not have permissions to the target database, the server checks the Guest user for that database.

6. The destination database will be checked for the presence of the Guest user, depending on the results, one of the following occurs:

If the Guest account exists, the trace will be replayed with the guest account.

If the Guest account does not exist in the target database, an error is returned and the trace is replayed as user A.

The following diagram illustrates the process of this check permission when replaying traces:

Permissions required to run SQL Server Profiler