Sql-server – Error while running stored Proc sp_BlitzIndex (TM) v4.2 – September 03, 2016

sp-blitzindexsql servert-sql

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.

Best Answer

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

Related Solutions

Sql-server – Reference the database from a stored proc

If db_id() is called with no parameter it returns the id of the current database - http://msdn.microsoft.com/en-us/library/ms186274.aspx

so you could just use: set @db_id=db_id()

And having read the rest of the question, you could call object_id() without the db name. If the object exists you'll get the id, if not NULL. So: SET @object_id = OBJECT_ID(N'dbo.Users');

Sql-server – Execution Plan not matching Stored Proc

Your code says you are doing a LEFT OUTER JOIN, but are you really? Your very first WHERE clause (as well as each that follow) filters rows for inclusion from the outer table:

WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')

This turns your LEFT OUTER JOIN into an INNER JOIN, whether you meant it or not. Perhaps you meant to move those filters to the ON clause.

Of course I would probably remove the non-sargeable MONTH() function from the JOIN, since this is going to force a complete scan on the remote table, and write it this way instead - also eliminating the join to your local months table:

DECLARE 
  @OName      VARCHAR(50) = 'John', 
  @Start_Date DATE = NULL,--'20120101', -- use safe date formats 
  @End_Date   DATE = '20121231'; -- use semi-colons

-- deal with NULLs here so the query is simpler:

SELECT @Start_Date = COALESCE(@Start_Date, '20010101'),
       @End_Date   = COALESCE(@End_Date, CURRENT_TIMESTAMP);

;WITH n(n) AS 
(
  -- get the # of months you need instead of relying on your Connector table:
  SELECT TOP (DATEDIFF(MONTH, @Start_Date, @End_Date)+1) Number
    FROM master..spt_values
    WHERE [type] = N'P' AND Number >= 0
    ORDER BY Number
), m(m) AS
(
  -- convert those to months:
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start_Date), 0))
  FROM n
)
SELECT 
  m.m, 
  MonthName = DATENAME(MONTH, m.m),
  [Widget Count] = COUNT(w.Widget_ID)
FROM m
LEFT OUTER JOIN [Server].[Database].dbo.Widget AS w -- meaningful alias
ON 
  w.Widget_Date >= m.m
  AND w.Widget_Date < DATEADD(MONTH, 1, m.m) -- open-ended date range query
  AND w.Operator_Name LIKE '%' + @OName + '%'
GROUP BY m.m
ORDER BY m.m;

Just ignore the m column in the output (you can't order by it without including it in the output, and it doesn't make sense to order by the name).

Another suggestion: don't use 'single quotes' as alias delimiters. Forms of this are deprecated and it also makes column alias look like string literals. Use [square brackets] instead.

Best Answer

Related Solutions

Sql-server – Reference the database from a stored proc

Sql-server – Execution Plan not matching Stored Proc

Related Question