GRANT SHOWPLAN for tempdb

Question

GRANT SHOWPLAN for tempdb

dkschill

Hall of Fame

Points: 3375
More actions
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?

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

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/

dkschill Hall of Fame Points: 3375 More actions · Answer 2

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!

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

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/

dkschill Hall of Fame Points: 3375 More actions · Answer 4

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

Thanks for the example!

dkschill Hall of Fame Points: 3375 More actions · Answer 5

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!