创建跟踪和查看执行计划需要的权限

最新推荐文章于 2021-05-24 14:25:04 发布

a86793222

最新推荐文章于 2021-05-24 14:25:04 发布

阅读量314

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/Uest/p/5723975.html

版权

多看书，多积累，温故知新。只有要用的时候，才会发现知识欠缺。不要说知道有这么回事，要很有底气的回答应该怎么做。
查看跟踪和查看执行计划需要怎样的权限？sysadmin/db_owner肯定可以，但不应该给这么大的权限。
创建登录名

--create login
use master
go
create login TracePlan with password='123qwe'
go

创建用户

--create user
use AdventureWorks2008R2
go
create user TracePlan for login TracePlan
go

授予对表Sales.SalesOrderHeader的查询权限

--grant select
grant select on Sales.SalesOrderHeader to TracePlan --user/role
exec as user='TracePlan'
select suser_name()
select top 10 * from Sales.SalesOrderHeader

此时查询正常，TracePlan对表Sales.SalesOrderHeader有select权限
显示执行计划(语句/工具栏)

set statistics profile on
select top 10 * from Sales.SalesOrderHeader

此时查询会报错：在数据库 'AdventureWorks2008R2' 中拒绝了 SHOWPLAN 权限。

授予显示计划的权限并再次查询

--授予显示计划的权限
revert
grant showplan to TracePlan
--再次查看语句执行计划
exec as user='TracePlan'
select suser_name()
set statistics profile on
select top 10 * from Sales.SalesOrderHeader
set statistics profile off

语句执行正常，并可以显示执行计划

跟踪，使用TracePlan开启跟踪

您必须是sysadmin固定服务器角色的成员或具有ALTER TRACE权限，才能对SQL Server运行跟踪。
给用户授予ALTER TRACE 权限

revert
use master
go
grant alter trace to TracePlan--只有在当前数据库是 master 时，才能授予服务器范围的权限

再次创建跟踪正常，运行一个语句，查看跟踪情况

use AdventureWorks2008R2
go
exec as user='TracePlan'
select suser_name()
select top 10 * from Sales.SalesOrderHeader

跟踪信息如下

首先可以看到有SET STATISTICS XML ON/OFF，这是由于在工具栏选择了"包括实际执行计划"
LoginName:用户的登录名(SQL Server安全登录或者Windows登录的凭据，其格式为"域\用户名")
SessionLoginName:发起该会话的用户的登录名。例如，如果您使用Login1连接到SQL Server并以Login2身份执行语句，则SessionLoginName将显示Login1，而LoginName将显示Login2
这就是我们看到LoginName不同的原因，我们使用Luest\uest连接到SQL Server执行部分语句，然后以TracePlan身份执行另一部分语句。
此时，如果我们revoke登录名TracePlan的alter trace权限

revert
use master
go
revoke alter trace to TracePlan

之前的跟踪还有效吗？

select * from sys.traces

可以看到跟踪还是在运行的

如果使用窗口中的暂停或停止按钮，马上变灰，而且不能"正常"关闭跟踪。只要点是，它一直弹出这个窗口

此时只能使用语句停止并移除跟踪

exec sp_trace_setstatus 2,0
exec sp_trace_setstatus 2,2

回收权限，删除用户、登录名

use AdventureWorks2008R2
go
revoke showplan to TracePlan
revoke select on Sales.SalesOrderHeader to TracePlan 
use AdventureWorks2008R2
go
drop user TracePlan
use master
go
drop login TracePlan

上面这些都可以在对象资源管理器中操作(表->权限->选择，数据库属性->权限->显示计划，服务器属性->权限->更改跟踪)。
In order to run the queries against the DMOs,either VIEW SERVER STATE or VIEW DATABASE STATE,depending on the DMO in question,will be required.
exec as user='TracePlan'与exec as login='TracePlan'的区别是什么？
以下内容来自Jerry_Chen博客http://www.cnblogs.com/jenrrychen/p/5172429.html

EXECUTE AS LOGIN/USER可以借以某个登陆用户和数据库用户的身份去执行某些操作，直到下一个REVERT语句发生才切换回caller的身份。当为LOGIN的时候，The scope of impersonation is at the server level。而当为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 that user。即login是服务器范围；user限制在当前数据库范围，上下文切换到数据库用户不会继承该用户在服务器级别的权限。这里有一段msdn在 EXECUTE AS (Transact-SQL)中的话：

While the context switch to the database user is active, any attempt to access resources outside of the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database that uses three- or four-part identifiers.
它的意思是一旦只要执行上下文切换成功且没有用REVERT切换回caller的上下文，任何对当前数据库范围外的其他数据库资源的访问都会失败，包括了USE <database>语句，分布式查询和带有3个以上标识符的对象引用。这里说的不对而且解释的也不清楚。不是说所有的都失败。失败的原因是当我们以某个数据库用户的身份切换后，对于其他数据库的资源访问都将以 guest用户身份进行，包括USE <database>语句。而我们都知道除了tempdb，msdb和master三个db，用户数据库和model数据库的guest用户是被禁用的。所以当我们试图去切换到其他数据库的安全上下文的时候就根本找不到一个用户身份。但是，我们是可以成功执行USE <database>语句去切换到tempdb，msdb和master三个db。

转载于:https://www.cnblogs.com/Uest/p/5723975.html