CODEWITHSUNDEEP
asp.netsql-server
thegunner
thegunner

Reputation: 7163

You do not have permission to use the bulk load statement

Hi I'm getting the error

You do not have permission to use the bulk load statement.

when I try to execute a stored procedure.

I've seen some websites mentioning about the bulkadmin role, but I don't see it.

I'm using SQL Server 2008.

Any ideas?

Upvotes: 28

Views: 51266

Answers (4)

Dimitri Troncquo
Dimitri Troncquo

Reputation: 461

I was having the same issue on a Sql Serverless database (Azure Synapse Analytics).

sp_addsrvrolemember is not available in this context, so I used the following to solve the issue:

ALTER ROLE db_owner ADD MEMBER [username]; -- owner allows bulk operations
ALTER ROLE db_denydatawriter ADD MEMBER [username]; -- in our case we don't want this specific user to have writer

It doesn't feel quite right to give owner to this user. Thoughts anyone?

Upvotes: -1

B Varma
B Varma

Reputation: 85

You can provide permission to user to fix this issue.

  1. Go to MS SQL server management and connect to db.
  2. In Object Explorer, go to Security and then login.
  3. Look for your user and right click on it.
  4. "Login Properties" popup will open
  5. Click on "Server Roles" and check "bulkadmin". Now if you try to execute again you must not get the error.

Source: http://sforsuresh.in/asp-net-mvc-4-not-permission-use-bulk-load-statement

Upvotes: 0

Stefan Rogin
Stefan Rogin

Reputation: 1527

Query version for Aleksandr's answer:

EXEC master..sp_addsrvrolemember @loginame = N'%userName%', @rolename = N'bulkadmin'
GO

Just replace %userName% with desired user. Also you need to be loged in as Sys-Admin (sa) or other user with EXEC permissions and bulkadmin granting permission.

Upvotes: 6

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

  • Start SQL Server Managament Studio
  • Expand Security->Logins
  • Locate your user, right click on it and take Properties
  • Open Server Roles tab
  • Make sure that bulkadmin is checked.
  • There you can experiment with other roles if bulkadmin doesn't work for you.
  • Click OK :)

Upvotes: 56

Related Questions