1

I have a stored procedure which is returning different results depending on the user executing it. My intention is to have it returning the same results. My own user gets the expected result (lots of rows), but the service account for the application gets an empty resultset (and no errors).

create procedure mal.get_geospatial_levels
as

set nocount on

select
    level_name = c.name
from sys.objects o
    inner join sys.columns c
        on c.object_id = o.object_id
where o.name like 'mb%'
    and c.name like '%[_]v%[_][0-9]%'
    and c.name not like 'mb%'
    and c.name not like 'admin%'
    and c.name not like '%[_]name'
    and c.name not in ('AREA_SQ_KM', 'OBJECTID', 'Shape')
group by c.name
order by c.name

The same thing happens if the query is executed on its own in SSMS.

What should I be looking at?

OutstandingBill
  • 2,614
  • 26
  • 38
  • 3
    My guess is that your permissions are mismatched. – Eric Hauenstein May 23 '14 at 21:02
  • What kind of permissions control the rows you see? My understanding of permissions is that I'd get an error message if I had insufficient permissions, but this is just fewer rows. The service account can execute the stored proc, so doesn't need permissions to the tables used: [link](http://stackoverflow.com/questions/3815411/stored-procedure-and-permissions-is-execute-enough). – OutstandingBill May 24 '14 at 04:02
  • Is anyone going to explain why -1? – OutstandingBill May 24 '14 at 04:05

1 Answers1

2

This is something special about sys.objects. In order to see a row from sys.objects, you have to have permissions to the object that row represents. This post explains it well.

Community
  • 1
  • 1
OutstandingBill
  • 2,614
  • 26
  • 38