Query Returning more rows when logging in as SA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Returning more rows when logging in as SA

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2004-01-22 : 12:49:46

Hi,

I am running the below query which is based upon two SQL views.

1) ZCustomerProfile
2) zIndividualREC

Now if i log in as 'Sa' into query analyzer and run the query 137 rows are returned. If i login as an ordinary user 35 rows are returned. Can anyone give me any ideas on how to get to the bottom of this. Shall I check permissions for each table referenced in the SQL views ? I presume this is a permissions problem ?

Any help would be appreciated.

HERE IS THE QUERY :

SELECT distinct 'Kunden-ID'= CASE WHEN A.iCompanyId IS NULL THEN
A.indivcompanyid ELSE A.iCompanyid end,'Kontakte-ID'=A.iIndividualId,'Firma 1'= case when A.vchCompanyName is null then A.indivCompanyName else A.vchCompanyName end ,
'Firma 2'=A.vchAddress1,Dept=A.vchDepartmentDesc,A.vchAttnLine, 'Adr. 1'=A.vchAddress2, 'Adr. 2'=A.vchAddress3,a.addr3, a.addr4,'Anrede'=A.vchSalutation,
Telefon=A.vchPhoneNumber,Fax=A.vchFax,'E-Mail'=A.vchEmailAddress,'Web Site'=A.vchURL,Language=A.vchUser2Desc,Gender=A.chGender
from zIndividualREC A, zCustomer_Profile E2563
where ((A.iIndividualId > 0) AND (E2563.iResponseId = 9975)
AND (1= A.iSiteId AND A.iSiteId = E2563.iSiteId AND A.iindividualid = E2563.iOwnerId
AND E2563.iQuestionId = 2563 AND E2563.iSurveyId = 56))

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-22 : 12:53:11

It is not an object permission problem. If it were, it would say permission denied. Could you post the views as well? It probably has to do with internal application permissions that the views are handling.

Tara

miranwar
Posting Yak Master

125 Posts

Posted - 2004-01-22 : 13:00:11

OK HERE IS ZIndividualREC I have taken columns out from the select list in zIndividualRec to make it readable. However the FROM clause is the same.

zIndividualRec :
ELECT
i.iIndividualId ,
i.iSiteId ,
i.chLanguageCode ,
i.vchAssignedId
i.bPrivate ,
i.vchUser1 ,
i.vchUser2 ,
"vchUser2Desc" = (CASE i.vchuser3 WHEN '200108' THEN 'ENG' WHEN '200128' THEN 'FRA' ELSE 'DEU' END),
i.vchUser3 ,
i.vchUser4 ,
i.vchUser5 ,
i.vchUser6 ,
i.vchUser7 ,
i.vchUser8 ,
i.vchUser9 ,
i.vchUser10 ,
i.chInsertBy ,
i.dtInsertDate ,
i.chUpdateBy ,
i.dtUpdateDate ,
i.tiRecordStatus

FROM Individual i
left outer join CustomerPhone cp
on i.iIndividualId = cp.iOwnerId
and i.iSiteId = cp.iSiteId
and i.tiRecordStatus = cp.tiRecordStatus
and cp.iPhoneTypeId = 115
left outer join country_ENG c1
on i.isiteid = c1.isiteid
and i.chcountrycode = c1.chcountrycode

left outer join Region R
on I.chCountryCode = R.chcountrycode
and I.chRegionCode = R.chRegionCode

left outer join incident IC
on IC.iContactID = I.iIndividualId
and IC.iincidentcategory = 3

left outer join company c
on ic.iownerid = c.icompanyid

where i.tiRecordStatus = 1
and (i.iAccessCode < 3
and c.iSiteID = i.iSiteID
or i.chUpdateBy = SUSER_NAME())

2) zCustomerProfile
select a.iSurveyId,
a.iProfileId,
a.iSiteId,
a.iOwnerId,
b.iQuestionId,
b.iResponseId,
b.vchResponseText,
b.chInsertBy,
b.dtInsertDate,
b.tiRecordStatus
from CustomerProfil
e a,
CustomerProfileQuestion b,
SurveyQuestion c
where a.iProfileId = b.iProfileId
and a.iSiteId = b.iSiteId
and c.iSurveyID = a.iSurveyID
and c.iSiteID = a.iSiteID
and c.iQuestionID = b.iQuestionID
and b.tiRecordStatus = 1
and a.tiR
ecordStatus = 1
and (a.iAccessCode < 3 or a.chUpdateBy = SUSER_NAME())

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-22 : 13:02:44

Yes exactly my point. These views are returning data according to SUSER_NAME(). So chUpdateBy = SUSER_NAME() has more data in the database for the sa account. It's just the way that the queries were written.

Tara

miranwar
Posting Yak Master

125 Posts

Posted - 2004-01-22 : 13:07:06

Thanks Tara, Your a Star

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources