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