如何赋予TRUNCATE权限(原创)

 

在企业的应用开发中，有一个临时表的概念，这个临时表式指业务上的临时表，而非sql server中的临时表，比如说，有一张正式表，他存有很大的数据量，查询频繁，我们就不希望频繁的向这张表插入数据(表很大，所以向它插记录会很慢)，这种情况下，就需要建一张物理上的表作为临时表，写记录的话先写在这张临时表上，再按照业务逻辑或时间间隔将临时表中的数据一次性导入到正式表之中，随后清空临时表，这样就降低了对正式表的操作频率。

为了最大限度的提高临时表的效率，所以清空临时表的时候不推荐使用Delete，而推荐使用TRUNCATE，这两者的区别可以baidu一下。但使用TRUNCATE会存在权限问题，如果一个用户权限不够，会无法执行SP。以下是详细的说明：

 

 

我们当前以dbo用户登陆创建如下SP。

CREATE PROC dbo.MyTruncate
AS
TRUNCATE TABLE dbo.MyTable
GO

随后再以另一个无权限操作 dbo.MyTable表的用户sbo登陆执行这个SP，sql server会报错说当前用户无权限操作dbo.MyTable表。

我们都知道，在SQL中，可以利用GRANT语句来为某个用户赋予某项权限。如

GRANT EXECUTE ON [dbo.MyTruncate] TO [sbo]

即为用户[sbo]赋予数据库对象[dbo.BCFL_TempToTable]以权限EXECUTE。但是，GRANT所能赋予的权限是没有TRUNCATE的。要想可以TRUNCATE一张表，必须是拥有ALTER权限，但仅为执行TRUNCATE就赋予某位用户ALTER，不但没有必要，还会引起一些安全上的问题。

 

 

TRUNCATE所需的最低权限是对 table_name 的 ALTER 权限。TRUNCATE TABLE 权限默认授予表所有者、sysadmin 固定服务器角色的成员、db_owner 和 db_ddladmin 固定数据库角色的成员，并且不可转移权限。

 

定义自定义权限集时为模块指定执行上下文非常有用。例如，某些操作（如 TRUNCATE TABLE）没有可授予的权限。若要执行 TRUNCATE TABLE，用户必须对指定表具有 ALTER 权限。授予用户对表的 ALTER 权限可能不是最佳方法，因为用户将拥有超出截断表的能力的权限。

 

碰到这种问题，可以使用SQL提供的EXECUTE AS语句来达成目的。还是上边那个例子，让我们再以dbo用户登陆，创建另一个SP：

CREATE PROC TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE dbo.MyTable

接着我们更改第一个SP为如下

CREATE PROC dbo.MyTruncate
AS
EXEC dbo.TruncateMyTable
GO

 随后赋予sbo有执行存储过程dbo.MyTruncate的权限。

 

GRANT EXECUTE ON [dbo.MyTruncate] TO [sbo]

 

Ok，现在再以sbo用户登陆，看看是不是已经可以正确运行存储过程dbo.MyTruncate了。

 

 　　希望这个小技巧可以帮助到您。O_o