Sql-server – The SELECT permission was denied on the object ‘syscolumns’, database ‘mssqlsystemresource’, schema ‘sys’

sql-server-2005

MSSQL 2005 sp3 32 bit running on Windows 2003

I have a sql server user MyUser in a database called MyDatabase. MyUser is able to access the MyDatabase database via SSMS. He is also dbo for that database.

When using the EMS Database Comparer 2011 to compare this database, I am getting the following error.

The SELECT permission was denied on the object 'syscolumns', database
'mssqlsystemresource', schema 'sys'

MyUser is not in the db_denydatareader or db_denydatawriter database roles

Is this normal, if so, what can be done to resolve it.

I am able to run the comparison as sysadmin.

I ran a trace using profiler and replayed using the MyUser user

Here is the result

The Query that causes the error

SELECT
    p.action
,   p.protecttype
,   p.columns
,   p.grantor
,   MAX(c.colid) AS colnum
,   u.name 
FROM
    sysprotects p 
    INNER JOIN 
        sysusers u 
        ON u.uid = p.uid 
    LEFT OUTER JOIN 
        syscolumns c 
        ON c.id = p.id 
WHERE 
    p.id = 1497772393  
GROUP BY 
    p.id
,   p.action
,   p.protecttype
,   p.columns
,   p.grantor
,   u.name

The error sent back by profiler

[Microsoft][SQL Native Client][SQL Server]The SELECT permission was
denied on the object 'syscolumns', database 'mssqlsystemresource',
schema 'sys'. (State 42000) (Code 229)

Some additional Clues

  1. Permissions error if the query is run from SSMS on the same machine as EMS
  2. No error when if the query is run from SSMS on the MSSQL server itself.

Best Answer

As the error indicates the login does not have SELECT permissions on the sys schema. This would require you to specifically grant SELECT to the login, or I believe you could also just GRANT VIEW DEFINITION for the database.

Update
Sorry just caught that last sentence. I have not worked with that software but seems it might be using a different login to execute the query if you can execute the same query fine in SSMS.

On a side note, since you are in SQL 2005, it would be a better practice to start using the new views for looking at catalog information.