Permission issue with information_schema.columns

Posted on 2005-04-25
Last Modified: 2012-06-21
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?

Question by:teiwaz
    LVL 68

    Accepted Solution

    The information_schema views do have some restrictions.

    Have you tried reading syscolumns directly?:

    Select @delim=CASE Sign( CharIndex('char', + CharIndex('date', ) 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('##pivot' AND'pivot'
    LVL 1

    Author Comment

    Sweet. Works great! User doesn't need any special permission to run it :D


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now