I'm working with a legacy system that attempts to execute schema changes in SQL for specific users by first querying sysobjects as that user to determine if the object exists. If it does it creates an ALTER VIEW statement otherwise it creates a CREATE VIEW statement. In this case the view exists, but the query continues to fail to list the object.
For example:
setuser 'APPLICATION_DEV'
Select * from sysobjects o, sysusers u
where u.uid = o.uid
and u.name = N'APPLICATION_DEV'
The problem is that certain accounts in this database can run this query without error and it returns all of the objects owned by that user. Other accounts however get no records returned by this query. If I setuser to SA and run the query all of that users objects appear. The affected user is the owner of the schema and has create view permissions on the schema. I cannot find any differences in permissions between a working user and a non-working user account.
Is there a permission that I am missing that would restrict a user from querying their own objects in sysobjects?
Yes, I know sysobjects is obsolete, but I have no control over the actual code here and instead have to fix the database so their code will work as expected.
EDIT: Additional findings. To complicate matters I can run this succesfully:
setuser 'APPLICATION_DEV'
Select * from sysusers
Where name = 'APPLICATION_DEV'
I can also execute this succesfully:
setuser 'APPLICATION_DEV'
Select * from sysobjects
Where uid = 308 --308 is the uid of the APPLICATION_DEV user
However, when I use a join either in the where clause or via INNER JOIN I then get no records. What would restrict my access to records purely based on the join? Index permissions?? I'm baffled.
