SQL数据库BULK INSERT非管理员的权限 [英] SQL Database BULK INSERT Permissions for non-Admins

问题描述

我一直在搜索和阅读我正在经历的问题，围绕它和/或特定于SQL Server产品的大量信息，但我找不到我正在寻找的具体问题的答案Azure SQL数据库。 我可以使用管理员帐户进行BULK INSERT，但是
不是非管理员用户。

I've been searching and reading about the issue I'm experincing, lots of info around it and/or specific to SQL Server product, but I'm not finding the answer I'm looking for specific to Azure SQL Database.  I can BULK INSERT with an Admin account, but not a non-Admin user.

在Azure SQL数据库中，我创建了一个用户并分配了几个数据库角色：

CREATE USER [datamaster] WITH PASSWORD ='myStrongPassword！';

ALTER ROLE db_datareader ADD MEMBER [datamaster];
$
ALTER ROLE db_datawriter ADD MEMBER [datamaster];

ALTER ROLE db_owner ADD MEMBER [datamaster];

CREATE USER [datamaster] WITH PASSWORD = 'myStrongPassword!';
ALTER ROLE db_datareader ADD MEMBER [datamaster];
ALTER ROLE db_datawriter ADD MEMBER [datamaster];
ALTER ROLE db_owner ADD MEMBER [datamaster];

然后，使用AAD（ACTIVE DIRECTORY ADMIN）在SSMS中作为SERVER ADMIN LOGIN或我自己连接执行BULK INSERT的存储过程：

执行用户='datamaster';

DECLARE @blobpath nvarchar（512）= N'abc123dataroot / CA / trades / inbound / address / Address1.csv';

EXEC [dbo]。[Procedure1] @blobpath;

EXECUTE AS USER = 'datamaster';
DECLARE @blobpath nvarchar(512) = N'abc123dataroot/CA/trades/inbound/address/Address1.csv';
EXEC [dbo].[Procedure1] @blobpath;

Msg 4834，Level 16，State 1，Line 146

你还没有pe使用批量加载语句的权限。

Msg 4834, Level 16, State 1, Line 146
You do not have permission to use the bulk load statement.

我需要具体分配给数据库用户" datamaster"允许他/她进行BULK INSERT？

Bill Blakey

Bill Blakey

推荐答案

Hello Bill，

Hello Bill,

请授予以下权限

GRANT ADMINISTER数据库批量操作[datamaster]

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [datamaster]

存储过程是否使用BULK INSERT将数据插入临时表？

Does the stored procedure use BULK INSERT to insert data into a temp table?

如果是，请问你能不能尝试使用永久表，并告诉我是否有效。

If yes, could you please try with a permanent table and let me know if that works.

这篇关于SQL数据库BULK INSERT非管理员的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！