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:

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

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
            , databaseName
            , objectID
            , indexID
            , partitionNumber
            , fragmentation
            , page_count
            , range_scan_count
            , scanDate
              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 >= @minFragmentation 
            AND ps.index_id > 0 -- ignore heaps
            AND ps.page_count > @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.

  • You say the job executed as yourself but the message says "Executed as user: dbo." Are you sure you configured the job correctly?
    – JodyT
    Feb 21, 2013 at 16:35
  • 2
    Try changing the job owner to "sa" and see if you get the same result; that will run it under the service account context. Also check the procedure to see if you are using an "execute as" in the DDL. Feb 21, 2013 at 16:50
  • JodyT - Yes, the database has dbo associated with my Windows login, Jason - I tried it and got the same result I'm afraid
    – Elliveny
    Feb 22, 2013 at 14:44

1 Answer 1


Ok, I found a resolution by doing the following:

  1. 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.')
  2. 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.
  3. 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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.