2

I am getting below error while running "sp_BlitzIndex(TM) v4.2 - September 03, 2016". My SQL server version is:

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

The error message is:

Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1373 [Batch Start Line 0]
ABCD database failed to process. The user does not have permission to perform this action.

Create temp tables.

ABCD
Inserting data into #IndexColumns for clustered indexes and heaps
Inserting data into #IndexColumns for nonclustered indexes
Inserting data into #IndexSanity
Failure populating temp tables.
Last @dsql:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
            SELECT    12 AS database_id, 
                    so.object_id, 
                    si.index_id, 
                    si.type,
                    'ABCD' AS database_name, 
                    COALESCE(sc.NAME, 'Unknown') AS [schema_name],
                    COALESCE(so.name, 'Unknown') AS [object_name], 
                    COALESCE(si.name, 'Unknown') AS [index_name],
                    CASE    WHEN so.[type] = CAST('V' AS CHAR(2)) THEN 1 ELSE 0 END, 
                    si.is_unique, 
                    si.is_primary_key, 
                    CASE when si.type = 3 THEN 1 ELSE 0 END AS is_XML,
                    CASE when si.type = 4 THEN 1 ELSE 0 END AS is_spatial,
                    CASE when si.type = 6 THEN 1 ELSE 0 END AS is_NC_columnstore,
                    CASE when si.type = 5 then 1 else 0 end as is_CX_columnstore,
                    si.is_disabled,
                    si.is_hypothetical, 
                    si.is_padded, 
                    si.fill_factor,
                    CASE WHEN si.filter_definition IS NOT NULL THEN si.filter_definition
                         ELSE ''
                    END AS filter_definition
                    , ISNULL(us.user_seeks, 0), ISNULL(us.user_scans, 0),
                    ISNULL(us.user_lookups, 0), ISNULL(us.user_updates, 0), us.last_user_seek, us.last_user_scan,
                    us.last_user_lookup, us.last_user_update,
                    so.create_date, so.modify_date
            FROM    [ABCD].sys.indexes AS si WITH (NOLOCK)
                    JOIN [ABCD].sys.objects AS so WITH (NOLOCK) ON si.object_id = so.object_id
                                           AND so.is_ms_shipped = 0 /*Exclude objects shipped by Microsoft*/
                                           A...

I have db_owner privileges on the database. Not sure what all permission I need to run the same.

  • 1
    I posted an answer for sp_Blitz but then realized you are asking about sp_BlitzIndex, so I deleted the answer. Looking into sp_BlitzIndex now. – Tara Kizer Sep 21 '16 at 18:29
  • Are there any databases on the server that you can run it on? – Erik Darling Sep 21 '16 at 18:35
2

You also need VIEW SERVER STATE permission in order to run sp_BlitzIndex. This is because it is querying DMVs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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