CODEWITHSUNDEEP
sql-server
Round
Round

Reputation: 15

Why is execute as a domain user not working?

I have created a stored procedure that brings over images from a network server and stores them in the database.

If the images are stored on local server then the stored procedure executes fine.

It will not execute with the images stored on network as the agent does not have permission to the network server.

To try and sort this I have added a network user that has permission to the image server. Granted them bulkadmin in server roles.

And try to run:

execute as user = 'domain\user'
exec dbo.stored_proc 'value1','\\servername\path\imgname.bmp'
revert

But I receive error:

The EXECUTE permission was denied on the object 'xxxxxxx', database 'zzzzzzz', schema 'dbo'.

I tried:

grant execute on dbo.stored_proc to [domain\user] ;

That errors with:

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

The domain user is not sysadmin, db_owner or own any schemas in the database.

I am not logged on as the user I am trying to setup.

What am I missing or forgetting?

Many thanks

Upvotes: 1

Views: 2601

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

It will not execute with the images stored on network as the agent does not have permission to the network server.

You can't use SQL Server impersonation for this. SQL Server impersonation only works within the SQL Server engine. You need real Windows impersonation for this. Luckily you're using SQL Agent which supports this through SQL Agent Proxies.

Note that you cannot use Agent Proxies with TSQL Job Steps. TSQL Job Steps always connect as the SQL Agent Account and use SQL Impersonation. So you need to run your stored procedure through a cmdexec or powershell job step.

SQL Agent will retrieve the stored credentials, perform a local logon using the credentials and then execute the powerhsell or cmdexec script using that identity.

Upvotes: 1

Related Questions