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

Question

Post reply

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

Sergiy

SSC Guru

Points: 110208
More actions
January 12, 2017 at 9:39 pm

#320424

Hi,
I have a strange issue.
I created a procedure on my local SQL instance using
WITH EXEC AS SELF
Inside this procedure I have the following statement:
SET @sql = 'SELECT CONVERT(VARCHAR(MAX), BulkColumn) AllData FROM OPENROWSET(BULK ' + QUOTENAME(@File, '''') + ', SINGLE_BLOB) AS DOCUMENT'
PRINT @sql EXEC (@sql)
When I execute this procedure I get the error:
Msg 4834, Level 16, State 1, Line 81
You do not have permission to use the bulk load statement.[/code]

But if I grab the printed SQL and run it in the very same window of SSMS it runs with no problems.
Same happens when I alter the procedure using
WITH EXEC AS CALLER
The problem goes away.
It's the same login which is used for all connections.
And which has all the necessary permissions.
What am I missing here?
_____________
Code for TallyGenerator
January 15, 2017 at 9:41 pm

This was removed by the editor as SPAM
January 15, 2017 at 10:08 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736281 More actions · Answer 1

What's in the execute_as_principal_id column in the sys.sql_modules DMV?

Execute as self is supposed to be execute as user_name, with the user ID being who created the proc.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Steve Jones - SSC Editor - Friday, January 13, 2017 7:56 AM
What's in the execute_as_principal_id column in the sys.sql_modules DMV?
Execute as self is supposed to be execute as user_name, with the user ID being who created the proc.

Thanks Steve,
execute_as_principal_id = 5

I found this reference:

https://www.sqlservercentral.com/Forums/Topic1480257-3077-1.aspx

If you are logged in with sysadmin rights when you create the procedure, the procedure will be owned by dbo, and if sa owns the database, sys.server_token will include sa

I checked, indeed, the suser_sname() inside the procedure was 'sa'.

Now I removed sysadmin privileges from my account, recreated the procedure "AS SELF", it now shows suser_sname() = my domain account, but the error is still the same.
When I execute the same statement outside of the procedure, under the same connection (within the same SSMS window) there is no problem, bulk load completes successfully.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 3

It's done.

That's why bulk load works when executed from script.

It also works when the procedure is created WITH EXEC AS CALLER.

But it fails when created WITH EXEC AS SELF.

All operations performed by the same DB user, even from the same SSMS window.

What could make the difference here?

_____________
Code for TallyGenerator

Steve Jones - SSC Editor SSC Guru Points: 736281 More actions · Answer 4

I'd try to recreate this with a few users and separate windows. To verify the issue. It might be a problem with the security module. I'll try to test in a bit as well. Now, I'm off to the dentist. 🙁

Steve Jones - SSC Editor SSC Guru Points: 736281 More actions · Answer 5

Yep, the "self" doesn't seem to work. I created two users, one with DBOwner, one with sysadmin. Both created a proc that calls Bulk INSERT with AS SELF. Neither works. Neither actually works even if the sysadmin and not a regular user with EXCECUTE permissions call it. The mapping to a database level permission is likely the issue here.

Steve Jones - SSC Editor SSC Guru Points: 736281 More actions · Answer 6

In playing more, I found this article: https://support.microsoft.com/en-us/kb/913422
This article notes: For security considerations, the server-scoped permissions are stripped down when you impersonate a database user unless the system administrator has explicitly set SQL Server to trust the impersonated context at the server-scope. In this case, a login with the control server server-scoped permission has no permissions to access any particular database. Therefore, the trigger module that is executed as this login cannot run.

I enabled trustworthy and I was able to run the command with the EXECUTE AS SELF setting.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Steve Jones - SSC Editor - Monday, January 16, 2017 11:49 AM
In playing more, I found this article: https://support.microsoft.com/en-us/kb/913422
This article notes: For security considerations, the server-scoped permissions are stripped down when you impersonate a database user unless the system administrator has explicitly set SQL Server to trust the impersonated context at the server-scope. In this case, a login with the control server server-scoped permission has no permissions to access any particular database. Therefore, the trigger module that is executed as this login cannot run.
I enabled trustworthy and I was able to run the command with the EXECUTE AS SELF setting.

OK, on 5th or 6th pass through this it started to make sense.
Server login mapped to a db user creates a procedure AS SELF, and on this stage the db user which is the author of the proc gets "disconnected", or "orphaned" from from Server login.
Then when procedure is executed it runs under the context of that db user which not necessarily the same user which is currently linked to the current Server login with the same name.
Server needs to "trust" that database to allow its users (which created the procedures back then) perform server-scoped operations.

Such "disconnection" does not happen when executed AS CALLER, obviously.

I made the database TRUSTWORTHY and it indeed fixed the issue.
Thanks.

_____________
Code for TallyGenerator