CODEWITHSUNDEEP
sql-server-2008t-sql
OutstandingBill
OutstandingBill

Reputation: 2882

query returns different results depending on user

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?

Upvotes: 1

Views: 1262

Answers (1)

OutstandingBill
OutstandingBill

Reputation: 2882

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.

Upvotes: 2

Related Questions