Create a trace and view the permissions required for the execution plan

Read more, accumulate more, restudying. Only when you need to use, you will find the lack of knowledge. Do not say to know that there is such a thing, to have a strong answer should be how to do.
See what permissions are required to track and view the execution plan? Sysadmin/db_owner certainly can, but should not give such a great privilege.
Create a login name

-- Create Login  Use Master Go Create  with password='123qwe'go

Create user

-- Create user  Use adventureworks2008r2 Go Create User  for Login Traceplan Go

Grant query permissions on table Sales.SalesOrderHeader

--Grant SelectGrant Select  onSales.SalesOrderHeader toTraceplan--User/roleexec  as User='Traceplan'Select Suser_name()Select Top Ten *  fromSales.SalesOrderHeader

At this time the query is normal, Traceplan to the table Sales.SalesOrderHeader have SELECT permission
Show Execution Plan (statements/toolbars)

Set Statistics  on Select Top Ten *  from Sales.SalesOrderHeader

At this point the query will error: SHOWPLAN permissions were denied in database ' adventureworks2008r2 '.

Grant permission to display the schedule and query again

--grant permission to display a schedulerevertGrantShowplan toTraceplan--Review the statement execution plan againexec  as User='Traceplan'Select Suser_name()Set StatisticsProfile onSelect Top Ten *  fromSales.SalesOrderHeaderSet StatisticsProfileoff

The statement executes normally and can display the execution plan

Tracking, using Traceplan to turn on tracking

You must be a member of the sysadmin fixed server role or have ALTER TRACE permission to run tracing on SQL Server.
Grant Alter TRACE permission to a user

revert  Use Master Go Grant Alter  to Traceplan- Server-scoped permissions can be granted only if the current database is master

Create trace again normal, run a statement to view the tracking situation

 use   adventureworks2008r2  go  as  user  "    " select  suser_name    select  top  10  *  Span style= "color: #0000ff;" >from  Sales.SalesOrderHeader 

Trace information is as follows

First you can see that there is set STATISTICS XML on/off, which is due to the "include Actual execution plan" selected in the toolbar
LoginName: User's login name (SQL Server secure logon or Windows logon credentials, in the format "domain \ Username")
SessionLoginName: The login name of the user who initiated the session. For example, if you use Login1 to connect to SQL Server and execute the statement as LOGIN2, Loginsessionname will display Login1, and LoginName will display Login2
This is why we see loginname different, we use luest\uest to connect to SQL Server to execute partial statements, and then execute another part of the statement as Traceplan.
At this point, if we revoke the login name Traceplan the ALTER TRACE permission

revert  Use Master Go Revoke Alter  to Traceplan

Does the previous trace still work?

Select *  from Sys.traces

You can see whether the trace or the running


If you use the Pause or stop button in the window, it is dimmed immediately, and you cannot turn the trace "normal". As long as the point is, it pops up this window

You can only use statements to stop and remove traces at this time

exec 2,0exec2,2

Reclaim permissions, delete user, login name

 use   adventureworks2008r2  go  revoke  showplan to   Traceplan  revoke  select  on  sales.salesorderheader  Traceplan  use   adventureworks2008r2  go  drop  user   Traceplan  use   master  go  drop  login Traceplan 

All of these can be manipulated in Object Explorer (table, permissions, select, Database properties, permissions, display plan, server properties, permissions, change tracking).
What is the difference between exec as user= ' Traceplan ' and exec as login= ' Traceplan '?
The following is from the Jerry_chen blog http://www.cnblogs.com/jenrrychen/p/5172429.html
Execute as Login/user can perform certain operations with the identity of a logged-on user and database user until the next revert statement occurs before switching back to caller's identity. When you are login, the scope of impersonation is at the server level. While being user, the scope of impersonation is restricted to the current database. A context switch to a database user does not inherit the Server-level permissions of this user.that is, login is the server scope; user restricts the current database scope, and context switches to database users do not inherit the user's permissions at the server level. Here's a section of MSDN in EXECUTE AS (Transact-SQL):
While the context switch to the database user was active, any attempt to access resources outside of the database would caus E the statement to fail. This includes use database statements, distributed queries, and queries the reference another database that uses Three-o R four-part identifiers.
It means that any access to other database resources outside the current database scope will fail, including the use <database> statement, once the context switch is executed successfully and no revert is switched back to the context of the caller. A distributed query and an object reference with more than 3 identifiers. This is not right and the explanation is not clear. Not that all of them fail. The reason for this failure is that when we switch as a database user, resource access to other databases is performed as guest user, including use <database> statements. And we all know that except for Tempdb,msdb and master three db, the Guest user database and the model database are disabled. So when we try to switch to the security context of another database, we can't find a user identity at all. However, we can successfully execute the USE <database> statement to switch to TEMPDB,MSDB and master three db.

Create a trace and view the permissions required for the execution plan