Global ##TempTable - how do permissions work?

Question

Post reply

Global ##TempTable - how do permissions work?

Maxer

SSCrazy Eights

Points: 8964
More actions
August 17, 2008 at 4:51 pm

#191164

I was just wondering about this... how do permissions work for global ##temp_tables?
When created it is global, to the entire SQL Server instance not just the database right?
So, how do you deal with permissions?
Is it like other objects where NO ONE has permissions (other than the creator, sysadmin, and I imagine DBO) until permissions are EXPLICITLY assigned?
I can't think of a good reason to use a global ##tempTable but say I create ##globalTempTable and then I call some sproc or something that I want to use it. So I call the sproc, pass the ##globalTempTable's name (likely appended with a guid or timestamp, current datetime, something to help make it unique).
Now what if I called that sproc with a different account than the one I used to create the table?
I suppose in that case it becomes an issue, so do I have to grant that account access to the global temp table?
Just wondering how all that would work as I've never worked with global temp tables (hopefully I will never have to), but now that I'm thinking about it... I really have no idea.

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

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

Toby White SSCertifiable Points: 6187 More actions · Answer 1

You cannot assign permissions to global temp tables - everyone has every permission on them including ddl.

They are visible for the entire SQL Sever instance.

You don't deal with permissions.

There are good reasons - like you suggested. Besides, maybe the name of the global temp table is always the same and known to the procedures

OK to call it with any account - it's just that if the session that created it is closed and there is no other session doing an active command using the temp table then it will be dropped.

There are legitimate cases for using global temp tables, but these cases are definitely not the norm. If you can use table variables so your execution plan will be reusable. It's so unfortunate that in 2005 you can't pass table variables as parameters.

Maxer SSCrazy Eights Points: 8964 More actions · Answer 2

Wow, so the ENTIRE SQL instance has full rights to the global temp table?

That just sounds crazy dangerous for some reason?

Then again, I suppose if security is so important to the process that you are trying to use the global temp table for AND if it is going to be a repeated process, chances are you should just make a REAL table in a database somewhere, assign all the permissions you need, then just re-use that table over and over...

Toby White SSCertifiable Points: 6187 More actions · Answer 3

I agree with your conclusions. The nature of a global temp table is that it is highly transient, so security shouldn't become a factor. To your point - I have actually had processes that checked for the existance of a real table at the beginning and if it exists then drop it and then create for use with a series of stored procedures and or processes.

Abhijit More SSCertifiable Points: 6897 More actions · Answer 4

global_temp tables logic is specially created for wide use/purpose, Microsoft has not any permission for these tables. the reason behind it its scope.

Abhijit - http://abhijitmore.wordpress.com