locked
Permissions needed for sql server job to execute stored procedure on linked server? RRS feed

  • Question

  • Hi all

    I have a job step which attempts to call a stored procedure on a linked server.

    This step is failing with a permission denied error. How can I debug or resolve this?

    The job owner is sysadmin on both servers so should have execute permission to the database/proc I'm calling, right?

    The error is:

    The EXECUTE permission was denied on the object 'myProc', database 'myDatabase', schema 'dbo'. [SQLSTATE 42000] (Error 229).  The step failed.

    My code is:

    EXEC [LinkedServer].myDatabase.dbo.myProc

    Also tried:

    SELECT * FROM OPENQUERY([LinkedServer], 'SET FMTONLY OFF EXEC myDatabase.dbo.myProc')

    With the same result.

    Any help appreciated.

    Friday, April 17, 2015 3:40 PM

Answers

All replies

  • Could you verify again going to source server and execute the proc to see if it works there. Also I would try explicit grant execute permission to store proc as no harm in trying. Also login must have the public role in the database.

    Check this link as well see if it helps:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/63c78535-28c9-4c4c-88a1-c367ad56aeb6/execute-permission-was-denied-on-the-object-using-linked-servers?forum=transactsql

    • Edited by dave_gona Friday, April 17, 2015 4:10 PM
    • Marked as answer by r9mcgon Monday, April 20, 2015 11:27 AM
    Friday, April 17, 2015 4:07 PM
  • The job owner may be sysadmin on the remote server. The service account for SQL Server Agent may not. And it is the latter that counts, since the it the service accounts that logs in and impersonates the job owner. But the impersonation inside SQL Server does not count much in Windows, and it is through Windows connection is made to the other site.

    One way to resolve this is to set up a login mapping for the job owner. The login mapping must be for an SQL login on the remote server.

    You can verify the theory, but running this query from the job:

       SELECT * FROM OPENQUERY([LinkedServer], 'SELECT SYSTEM_USER')

    By the way, putting SET FMTONLY OFF in OPENQUERY is a terrible idea. This has the effect that the procedure is executed twice. (Unless both servers are SQL 2012 or higher in which case FMTONLY has no effect at all.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 17, 2015 9:16 PM
  • That link helped me resolve the issue - the login used by the linked server didn't have execution permission on the proc.

    Thanks Dave.

    Monday, April 20, 2015 11:28 AM