I have a crosstab sproc that returns a cross tab result set (author is robvolk). It works great if the user is dbo, but fails otherwise. I've determined it has to do with permissions on the tempdb and querying the information_schema.columns view. Here is the line that fails:
Select @delim=CASE Sign( CharIndex('char',data_type)+CharIndex('date',data_type) ) When 0 Then '' Else '''' END
where table_name='##pivot' AND column_name='pivot'
I've checked and I can read the data in this table with a non-sa user, but if the user is non-sa then the above query returns no records. If the user is sa, then I get 1 record (which is expected).
If I make the non-sa user the dbo of the temp table, it works, but obviously that is not a working solution. What do I need to do here?