• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

Permission issue with information_schema.columns

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
From tempdb.information_schema.columns
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?

1 Solution
Scott PletcherSenior DBACommented:
The information_schema views do have some restrictions.

Have you tried reading syscolumns directly?:

Select @delim=CASE Sign( CharIndex('char',typs.name) + CharIndex('date',typs.name) ) When 0 Then '' Else '''' END
From tempdb.dbo.syscolumns cols with (nolock)
Inner join systypes typs with (nolock) on typs.xtype = cols.xtype AND typs.xusertype = cols.xusertype
Where object_name(cols.id)='##pivot' AND cols.name='pivot'
teiwazAuthor Commented:
Sweet. Works great! User doesn't need any special permission to run it :D


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now