Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks Shawn, I did the following but am still getting the same error. USE MyDatabase; GRANT VIEW DEFINITION ON DATABASE:MyDatabase TO MyUser; –  Craig Efrein Jan 17 '12 at 14:54
    
I also gave MyUser SELECT rights on the sys schema of the database. EMS is a proprietary tool so I can't change the code. Perhaphs this is just a bug in their software? –  Craig Efrein Jan 17 '12 at 15:00
    
Does the software keep an active connection to the instance of SQL? It might need to be reset/restarted since you changed the permissions to take effect. That would be the only thing I can think of, otherwise might check if EMS has a forum or support email. –  Shawn Melton Jan 17 '12 at 15:26
    
Actually, I just ran the query from SSMS on the same machine that has EMS and I am now seeing the same error. So the GRANT DEFINITION and granting SELECT to sys for MyUser has not resolved the problem. Maybe there isn't a way to manually grant access to the syscolumns table in mssqlsystemresource? –  Craig Efrein Jan 17 '12 at 15:31
    
USE myDatabase; GRANT SELECT ON syscolumns TO [MyUser]; –  Shawn Melton Jan 17 '12 at 15:41

Which SQL server principal is EMS Database Comparer 2011 using to connect to your database? This account is the one that needs the appropriate permissions. You can use profiler to see the account that is trying to connect to the DB. For meta data visibility permissions check this link.

share|improve this answer
    
Thank you for the response, I actually already did use Profiler to review the queries being used by EMS. The server principal now has the right to access the tables from mssqlsystemresource. But I am getting another error from MSComparer.exe –  Craig Efrein Jan 17 '12 at 16:31

You need to allow your user access to the objects contained in the 'sys' schema. The dbo database role allows a user to view and change all the contents in a database under the 'dbo' schema(as the owner).

Reference: http://technet.microsoft.com/en-us/library/ms190387.aspx

share|improve this answer
    
I have already granted SELECT to the sys schema to MyUser and I am still getting the same error. –  Craig Efrein Jan 17 '12 at 15:36
    
What server role does your user have? Public? –  OliverAsmus Jan 17 '12 at 15:39
    
Yes just Public –  Craig Efrein Jan 17 '12 at 15:41
    
Try this command - GRANT VIEW DEFINITION ON schema::sys TO myuser –  OliverAsmus Jan 17 '12 at 15:43
    
For giggles, also try it on using 'schema::dbo' –  OliverAsmus Jan 17 '12 at 15:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.