Skyscraper

lunes, 27 de febrero de 2012

GRANT SHOWPLAN PERMISSION SQL SERVER 2005

Today I was trying to perform a big "UPDATE" in a SQL SERVER 2005 database between tables from different databases, even when the "UPDATE" works I was was concerned about the large amount of time spent on it's execution.

I've tried a "Display Estimated Execution Plan" (CTRL+L) and showed me the following error:

SHOWPLAN permission denied in database 'BussinessDW'.

After I googled for "GRANTING SHOWPLAN" I found that the following command solves the problem:

GRANT SHOWPLAN TO @user - - @user is the database user to who I want to permit the showplan

However after a little research in MSDN I found that security risks are involved with this sentence.  Because "SHOWPLAN" includes "CONTROL" and "ALTER TRACE" permissions, I decided to revoke the "SHOWPLAN" permission to the user after I've completed the analysis of the execution plan.

To revoke the show plan I used the following command:
 
REVOKE SHOWPLAN TO @user - - @user is the database user to who I want to remove the showplan permission

To learn more about SQL Server permissions, visit  Laurentiu Cristofor's blog

P.S. 2014-04-24: Thanks to the "unknown" user who requested the command to revoke the show plan permission, I forgot to put it in the original entry.

5 comentarios:

  1. Can you please share the script for revoking show plan permissions?
    thanks

    ResponderEliminar
    Respuestas
    1. David'S Blog: Grant Showplan Permission Sql Server 2005 >>>>> Download Now

      >>>>> Download Full

      David'S Blog: Grant Showplan Permission Sql Server 2005 >>>>> Download LINK

      >>>>> Download Now

      David'S Blog: Grant Showplan Permission Sql Server 2005 >>>>> Download Full

      >>>>> Download LINK zH

      Eliminar
  2. Hello Everyone !

    USA SSN Leads/Fullz available, along with Driving License/ID Number with good connectivity.

    All SSN's are Tested & Verified.

    **DETAILS IN LEADS/FULLZ**

    ->FULL NAME
    ->SSN
    ->DATE OF BIRTH
    ->DRIVING LICENSE NUMBER
    ->ADDRESS WITH ZIP
    ->PHONE NUMBER, EMAIL
    ->EMPLOYEE DETAILS

    *Price for SSN lead $2
    *You can ask for sample before any deal
    *If you buy in bulk, will give you discount
    *Sampling is just for serious buyers

    ->Hope for the long term business
    ->You can buy for your specific states too

    **Contact 24/7**

    Whatsapp > +923172721122

    Email > leads.sellers1212@gmail.com

    Telegram > @leadsupplier

    ICQ > 752822040

    ResponderEliminar
  3. David'S Blog: Grant Showplan Permission Sql Server 2005 >>>>> Download Now

    >>>>> Download Full

    David'S Blog: Grant Showplan Permission Sql Server 2005 >>>>> Download LINK

    >>>>> Download Now

    David'S Blog: Grant Showplan Permission Sql Server 2005 >>>>> Download Full

    >>>>> Download LINK LQ

    ResponderEliminar