Post reply

GRANT SHOWPLAN for tempdb

  • May 21, 2012 at 8:57 am

    #256152

    I am working with report writers in our test environment, and had to grant them SHOWPLAN on tempdb.

    It felt wrong adding the user to tempdb, and then granting SHOWPLAN to that user...am I just being paranoid, or is there a better way to do it?

  • May 21, 2012 at 10:15 am

    #1490524

    I've given permissions to view query plans to few developers and testers at Dev and QA environments. I admit that it is rare that I'm granting this permission, but it is because it is very rare that a developer or tester wants to see query plans. Personally I don't see any reason why a developer won't be able to see the query plan of a query or procedure that he just created.

    I have to admit that I've never done it on tempdb. The main reason is that there is no development on tempdb and that if there is anything in that database, it will vanish the next time that the server reboots. Can you explain why do you give them permission to view query plans on tempdb?

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • May 21, 2012 at 10:19 am

    #1490527

    They received an error about being able to view SHOWPLAN on tempdb, and I assumed it was the temp tables they were creating and querying against.

    Is that not the case?

    Will I have to recreate the user when the instance is restarted?

    Thanks for the help!

  • May 21, 2012 at 10:33 am

    #1490538

    I don't think that you need permission to view query plan on tempdb if you are using temporary tables. I've just tried it with this code:

    create login testing with password = 'cuse!2A'

    create user testing from login testing

    grant showplan to testing

    exec sp_addrolemember db_datareader, testing

    Then I disconnected and opened a new connection using testing login. I've ran the code bellow and I could see the query plans.

    select * from sys.objects

    select * into #t from sys.objects

    select * from #t

    drop table #t

    As for your question about recreating the user – the answer is yes. Each time that you restart SQL Server the tempdb is created from scratch and you'll have to recreate the users and give them permissions again.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • May 21, 2012 at 10:37 am

    #1490541

    So my question needs to be what are they using that is requiring access to run SHOWPLAN on tempdb!

    Thanks for the example!

  • May 21, 2012 at 11:17 am

    #1490565

    Found it! If you create an index on an existing temp table that currently contains data you will request showplan data from tempdb. Not sure this is the only thing, but here is the testable code:

    create table #t (i int identity(1,1), s char(10));

    insert into #t

    select 'a' union select 'b' union select 'c';

    create clustered index t on #t(s);

    drop table #t;

    Thanks for the help!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply