locked
Error when trying to save image file in a blob column RRS feed

  • Question

  • User-2060727494 posted

    I have this SQL code:

    USE [myDB]
    INSERT INTO FWImage ([FWImageFile])
    SELECT 'SQL Server File'
    FROM OPENROWSET(BULK N'C:\Users\Bob\Downloads\Pictures\logo.gif', SINGLE_BLOB) image;

    But I get this error when I try to run it:

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

    I'm not a SQL DBA as I am a developer so I'm not real strong in this area.  So I opened SSMS and connected to my SQL server.  I then clicked Security, Logins.  I then right-clicked my SQL username and clicked Properties.  Next, I clicked Server Roles and clicked the checkbox for bulkadmin.  But I got this error:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Add member failed for ServerRole 'bulkadmin'. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.4100.1+((SQL14_PCU_main).150420-1653)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+ServerRole&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot alter the server role 'bulkadmin', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=15151&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    So what am I doing wrong?  Like I said, I'm not too swift with permissions stuff.

    Thursday, March 17, 2016 3:51 PM

Answers

  • User364663285 posted

    Ok, I moved my image to another disk drive like you suggested.  SO here is my new SQL statement:

    USE [myDB];
    INSERT INTO FWImage ([FWImageFile])
    SELECT *
    FROM OPENROWSET(BULK N'F:\logo.gif', SINGLE_BLOB) image;

    But I still get this error:

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

    This is file access problem. Did you try to put the file to SQL server folder in the way I suggested above?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2016 2:42 AM

All replies

  • User77042963 posted

    Try:

    USE [myDB];
    INSERT INTO FWImage ([FWImageFile])
    SELECT  *
    FROM OPENROWSET(BULK N'C:\Users\Bob\Downloads\Pictures\logo.gif', SINGLE_BLOB) image;

    Thursday, March 17, 2016 6:33 PM
  • User-2060727494 posted

    Hey limno,

    Thanks for the response.  I made the change as you suggested.  But I still get this error:

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

    Thursday, March 17, 2016 7:23 PM
  • User77042963 posted

    Check your permission for this folder:

    C:\Users\Bob\Downloads\Pictures
    Thursday, March 17, 2016 7:55 PM
  • User-219423983 posted

    Hi bobh0526,

    I then right-clicked my SQL username and clicked Properties.  Next, I clicked Server Roles and clicked the checkbox for bulkadmin.  But I got this error:

    Cannot alter the server role 'bulkadmin', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

    First of all, you should follow with limno’s suggestion to make sure you have enough rights for the folder. As usual, you’d better operate the image file that comes from other Disc, not Disc C.

    According to the error message, it seems that this error is related to the insufficient rights. You could first make sure your current login user has the permission to modify the roles for a username. If any user wants to modify the permissions of a special user, that user needs to have higher or equivalent rights as this special user.

    For this, you could have a look at this blog which talks about this similar situation. Maybe it’s not directly to your issue, but I think it would give you some ideas for solve the problem.

    Best Regards,

    Weibo Zhang

    Friday, March 18, 2016 5:57 AM
  • User364663285 posted

    bobh0526

    Hey limno,

    Thanks for the response.  I made the change as you suggested.  But I still get this error:

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

    You can try also to put that picture file into SQL server folder like

    C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012\MSSQL\DATA

    Friday, March 18, 2016 6:40 AM
  • User-2060727494 posted

    Ok, I moved my image to another disk drive like you suggested.  SO here is my new SQL statement:

    USE [myDB];
    INSERT INTO FWImage ([FWImageFile])
    SELECT *
    FROM OPENROWSET(BULK N'F:\logo.gif', SINGLE_BLOB) image;

    But I still get this error:

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

    Wednesday, March 23, 2016 1:53 PM
  • User77042963 posted

    You need to give the permission to the user to run bulk load from SSMS and the folder permission for this user as well. 

    Wednesday, March 23, 2016 8:55 PM
  • User364663285 posted

    Ok, I moved my image to another disk drive like you suggested.  SO here is my new SQL statement:

    USE [myDB];
    INSERT INTO FWImage ([FWImageFile])
    SELECT *
    FROM OPENROWSET(BULK N'F:\logo.gif', SINGLE_BLOB) image;

    But I still get this error:

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

    This is file access problem. Did you try to put the file to SQL server folder in the way I suggested above?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2016 2:42 AM
  • User-595703101 posted

    Hi Bob,

    Your SQL Server login user should have first the permissions to execute BULK operation commands

    You could better run one of the following commands in order to grant required permission to your user.

    ALTER SERVER ROLE [bulkadmin] ADD MEMBER [sql_login]
    
    -- or
    
    use master
    GRANT ADMINISTER BULK OPERATIONS TO [sql_login]

    Then you can insert image or binary files into SQL database table.

    Thursday, March 24, 2016 9:31 AM
  • User-2060727494 posted

    Hi eralper,

    Thanks for the reply.  I tried this:

    ALTER SERVER ROLE [bulkadmin] ADD MEMBER [SBarker_bob-admin] and got this error:

    Msg 15151, Level 16, State 1, Line 1
    Cannot alter the server role 'bulkadmin', because it does not exist or you do not have permission.

    I then tried this:

    use master
    GRANT ADMINISTER BULK OPERATIONS TO [SBarker_bob-admin] and I get this error:

    Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

    I am a web developer and not strong in this area.  So I'm sure I'm missing something obvious.  But not sure what it is.

    Monday, March 28, 2016 3:01 PM
  • User-219423983 posted

    Hi bobh0526,

    Cannot alter the server role 'bulkadmin', because it does not exist or you do not have permission

    To add a member to a fixed server role, you must be a member of that fixed server role, or be a member of the sysadmin fixed server role.

    You could try to following below steps to achieve granting current login user with Role “bulkadmin”.

    • 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 :)

    Best Regards,

    Weibo Zhang

    Tuesday, March 29, 2016 9:17 AM
  • User364663285 posted

    Hi eralper,

    Thanks for the reply.  I tried this:

    ALTER SERVER ROLE [bulkadmin] ADD MEMBER [SBarker_bob-admin] and got this error:

    Msg 15151, Level 16, State 1, Line 1
    Cannot alter the server role 'bulkadmin', because it does not exist or you do not have permission.

    I then tried this:

    use master
    GRANT ADMINISTER BULK OPERATIONS TO [SBarker_bob-admin] and I get this error:

    Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

    I am a web developer and not strong in this area.  So I'm sure I'm missing something obvious.  But not sure what it is.

    Can you try to use the similar folder like

    C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012\MSSQL\DATA

    Wednesday, March 30, 2016 9:00 AM