无论我尝试什么,我都会收到这个错误.
我有一个存储过程,执行为：
CREATE PROCEDURE usp_myproc WITH EXECUTE AS 'myuser'
在这个s.p.我有
EXEC('INSERT INTO ' + @tablename + '
SELECT col1,col2,col3
FROM OPENROWSET(
BULK '''+ @filepath +''',FORMATFILE='''+ @formatfile +''',FIRSTROW=2
)as t'
)
myuser确实具有bulkadmin角色,读/写,创建表,插入,选择,执行,更改权限.其中一些可能不需要,但这是我到目前为止所尝试的.我错过了什么？
谢谢.
解决方法
正如我在评论中所说,服务器级别权限在您使用模拟时被剥离.
有两种方法：
坏的和快速的方式：
将数据库pushworthy设置为ON.它将完成工作.但如果你不完全理解它的作用,那么我的建议是不要这样做.
但是,这是代码：
ALTER DATABASE [YourDatabase] SET TRUSTWORTHY ON;
好但却慢的方式
这更加精确,没有任何令人讨厌的安全副作用.
您所做的是使用证书对存储过程进行签名.您可以在数据库中从该证书创建用户.您为该用户授予对数据库中表的适当权限.您还可以从同一证书创建登录,并为该登录授予批量权限.
因为您使用该证书对存储过程进行签名,所以每次执行sp时,都会在该用户的上下文中执行并登录从该证书创建的位置.
步骤是：
>在master中创建证书
>从该证书创建登录
>授予该登录的批量管理员权限
现在,您需要在用户数据库中使用完全相同的证书,因此我们需要执行一些额外的步骤
现在我们可以敲定
>从证书创建用户
>向该用户授予对表的权限
>从存储过程中删除execute as子句
>使用您的证书对存储过程进行签名
这是代码：
USE master
go
CREATE CERTIFICATE BulkInsertCert
ENCRYPTION BY PASSWORD = 'NicePassword!0'
WITH SUBJECT = 'Gives Bulk Insert Privilegde'
go
CREATE LOGIN BulkInsert_CertLogin FROM CERTIFICATE BulkInsertCert
go
GRANT ADMINISTER BULK OPERATIONS TO BulkInsert_CertLogin
go
BACKUP CERTIFICATE BulkInsertCert TO FILE = '[your directory]\BulkInsertCert.cer'
WITH PRIVATE KEY (FILE = '[your directory]\BulkInsertCert.pvk',ENCRYPTION BY PASSWORD = 'EvenNicerPassword!0',DECRYPTION BY PASSWORD = 'NicePassword!0')
go
USE [YourDatabase]
CREATE CERTIFICATE BulkInsertCert FROM FILE = '[your directory]\BulkInsertCert.cer'
WITH PRIVATE KEY (FILE = '[your directory]\BulkInsertCert.pvk',DECRYPTION BY PASSWORD = 'EvenNicerPassword!0',ENCRYPTION BY PASSWORD = 'TheVeryBestPasswordThereIs!0')
go
--NOW DELETE THE CERTIFICATES FROM DISK
CREATE USER BulkInsert_CertUser FOR CERTIFICATE BulkInsertCert
go
GRANT ALTER,INSERT ON [YourTable] TO BulkInsert_CertUser
go
ALTER PROCEDURE usp_myproc
AS
EXEC('INSERT INTO ' + @tablename + '
SELECT col1,FIRSTROW=2
)as t'
)
-- Sign the test procedure each time you have changed it.
ADD SIGNATURE TO usp_myproc BY CERTIFICATE BulkInsertCert
WITH PASSWORD = 'TheVeryBestPasswordThereIs!0'
go
最后的说明：
请将您的目录替换为您确定sql服务帐户有权写入的路径！
完成设置后,请确保删除这些导出的证书.
