SQL Server 2012 defrag job runs interactively, but not as a job - why? I have recently started using the dba_indexDefrag_sp stored procedure from SQL Fool (http://sqlfool.com/2011/06/index-defrag-script-v4-1/) against my production databases running on SQL Server 2012 (Standard 64-bit). When I execute the procedure I use all of the default settings, the only parameter I provide is the @database, which I use to pick out the active databases which need maintenance - for example:

USE MASTER
GO
EXEC dba_indexDefrag_sp @DATABASE='MyDb'


If I run this from SQL Management Studio, on the server, the procedure executes to completion, doing its work and recording its activity, taking about an hour and a half. If, however, I create a SQL Agent job for it, using the same login account (my own) to run the job then I see the job execute successfully BUT I see that the job completed in 0 seconds and the job history indicates an issue:

Job Name        Weekly Index Defragment
Step Name       Defragment MyDb Db
Duration        00:00:00
Sql Severity    0
Sql Message ID  0
Operator Emailed    
Operator Net sent   
Operator Paged  
Retries Attempted   0

Message
Executed as user: dbo. The user does not have permission to perform this action. (Line Number: 442) [SQLSTATE 01000] (Message 0).  The step succeeded.


The problematic line 442 is an INSERT, as follows:

        INSERT INTO dbo.dba_indexDefragStatus
        (
              databaseID
            , databaseName
            , objectID
            , indexID
            , partitionNumber
            , fragmentation
            , page_count
            , range_scan_count
            , scanDate
        )
        SELECT
              ps.database_id AS 'databaseID'
            , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName'
            , ps.[object_id] AS 'objectID'
            , ps.index_id AS 'indexID'
            , ps.partition_number AS 'partitionNumber'
            , SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation'
            , SUM(ps.page_count) AS 'page_count'
            , os.range_scan_count
            , GETDATE() AS 'scanDate'
        FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL, @scanMode) AS ps
        JOIN sys.dm_db_index_operational_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL) AS os
            ON ps.database_id = os.database_id
            AND ps.[object_id] = os.[object_id]
            AND ps.index_id = os.index_id
            AND ps.partition_number = os.partition_number
        WHERE avg_fragmentation_in_percent &gt;= @minFragmentation 
            AND ps.index_id &gt; 0 -- ignore heaps
            AND ps.page_count &gt; @minPageCount 
            AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
        GROUP BY ps.database_id 
            , QUOTENAME(DB_NAME(ps.database_id)) 
            , ps.[object_id]
            , ps.index_id 
            , ps.partition_number 
            , os.range_scan_count
        OPTION (MAXDOP 2);


I don't understand why it fails as a SQL Agent job yet succeeds 'interactively'. Is there something about that INSERT statement which needs special attention? I would appreciate some help to allow me to resolve this problem. Thanks.
Ok, I found a resolution by doing the following:


I moved dba_indexDefrag_sp and its associated objects into a 'HouseKeeper' database, rather than putting them in the Master database. (I note Michelle Ufford recommends this, saying: 'It’s up to you where you create it. You could technically create it in the MASTER database, but I recommend creating a utility database for your DBA administrative tasks.') 
I created a new 'SQLJobRunner' Windows login, then added it to SQL Server, setting it as db_owner on the dbs I'm defragmenting. I also applied to same to the HouseKeeper db, to give the login full access to that database.
I changed the defrag SQL Agent job to run using the 'SQLJobRunner' login.


and then it worked, which deals with my issue. It is notable that I have the same security setup against my own login and that still doesn't work. Odd... but that isn't really a problem for me now - I don't know why it that doesn't work though.

Note that I'm aware that giving the login db_owner is a bit of a sledgehammer approach, but now that it works I'm able to make the security a bit more fine-grained.

Thanks!