Post reply

T-SQL Problem Using "run as user" in job step

  • February 7, 2011 at 1:27 pm

    #234585

    I have a simple SQL script to insert rows in a table. It worked fine within a SQL Server Agent job. Later, on the "advanced" page of the SQL step, I changed the "Run as user" from blank to something else. Now, I see an error relating to my target table:

    Executed as user: my_run_as_user. Cannot find the object "MyTargetTable" because it does not exist or you do not have permissions. [SQLSTATE 42000] (Error 1088). The step failed.

    But, the "run as user" can run the very same script from a query window. So, it seems to me that the user would have permissions.

    This is SQL Server 2008R2 and the job is owned by sa.

    Any help would be appreciated.

  • February 7, 2011 at 1:52 pm

    #1283423

    could it be that the step is running in the master database instead of the db the proc is in? what if the command was changed to have the full 3 part naming convention? DbName.dbo.MyTargetTable?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 7, 2011 at 3:25 pm

    #1283465

    Lowell,

    On the Job Step Properties, I am specifying the user database. The SQL is reading (unqualified) tables in the user database and inserts into a table that's in another database. For the target table, I am using full 3-part names.

    Things I noticed: The job works if owner is "sa" and the "run as user" is empty. The job works with a non-blank non-sa "run as user" as long as I also change the owner to be the same as the "run as user". The job fails if the owner is 'sa' and the "run on user" is my non-blank non-sa user.

    I had hoped that I could keep the owner as 'sa' and use my standard reporting user as the "run on user", but no luck yet.

    Thanks for taking the time to consider my question.

Viewing 3 posts - 1 through 2 (of 2 total)

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