How to Run sp_BlitzIndex® with Minimum Permissions

Are you the conscientious DBA who would like to only run procedures with the minimum permissions needed? Or would you like to grant others the ability to run sp_BlitzIndex®, but not need to give them high level permissions?

Good news: you can diagnose very tricky index disorders with quite minimal permissions! Here’s how you can set up sp_BlitzIndex® to have a minimal security footprint.

Prep: Create sp_BlitzIndex®

Get sp_BlitzIndex® and create the stored procedure on your instance. By default it will install in the master database, but you can change that.

1. Create a Login

Create a fresh new login and name it sp_BlitzIndex_User.

If you choose to grant these permissions to an existing login, move on to step 2 and adjust the login name in the following scripts.

2. Grant permissions in the master database (or wherever you created sp_BlitzIndex®)

Create a user for sp_BlitzIndex_User in the master database, or wherever you installed the sp_BlitzIndex® stored procedure.

Then grant the user EXECUTE on sp_BlitzIndex® and VIEW SERVER STATE.

This can be done with a script:

3. Grant permissions in the database you’re going to diagnose

Create a user in the database whose indexes you want to check for your login. Then grant the login VIEW DEFINITION in that database.

Gotcha: if the user exists but does not have VIEW DEFINITION, sp_BlitzIndex® will not fail, but will return no meaningful results.

If you want to check more than one database, grant VIEW DEFINITION in each database.

You can do this with a script:

 

4. Diagnose your indexes!

That’s it, you’re ready to go!

Diagnose your indexes by running a command like the following– just swap in the correct database name.