Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 >= @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.

share|improve this question
    
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 '13 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. –  Jason Cumberland Feb 21 '13 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 '13 at 14:44
add comment

1 Answer

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.

Thanks!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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