Identical Logins producing different results

[jrsansom]

Morning,

I have come across a bizarre problem in SQL Server 2005 that has me stumped.

I have created a SQL Server Login (ReportReader) that has no server level roles assigned but does have the db_datareader role on a particular database. (The purpose of this account is to eventually be used via Reporting Services)

When I execute a particular query (that accesses a user database) with this login it produces different results to those when I use the sa login. The query is identical.

I tried to troubleshoot this scenario for a good few hours, I even created another SQL Server login “TEST” with exactly the same permissions as ReportReader but this produces the expected results when the query is run.

Has anyone come across this before and can you offer some advice as to how I can troubleshoot this? Yes I can create another SQL Server Login to get around the issue but I want to understand the problem and how it has arisen.

Any help is greatly appreciated.

 

[fredericofonseca]

Please show us the SQL, and a explanation of why the results are different, including some sample data.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[jrsansom]

Hi,

Thank you for your reply.

Here is the identical code run for both logins.

SELECT	
	TransactionOrigin.Description , 
	COUNT(AccountTransaction.SettlementAmount) as 'Number of Transactions',
	SUM(AccountTransaction.SettlementAmount) as 'Value'
FROM CustomerAccount 
	INNER JOIN AccountTransaction ON 
	CustomerAccount.ID = AccountTransaction.CustomerAccountID
	INNER JOIN TransactionOrigin ON 
	AccountTransaction.TransactionOriginID = TransactionOrigin.ID
WHERE
(	
	(
		TransactionOriginID = 14 OR 
		TransactionOriginID = 15 OR 
		TransactionOriginID = 16 OR 
		TransactionOriginID = 17
	)
	AND SettlementAmount > 0 
	AND ExternalTransactionID IN 
	(
		SELECT ExternalTransactionID 
		FROM AccountTransaction 
		WHERE 
		(
			(	
				TransactionOriginID = 2 OR 
				TransactionOriginID = 3 OR 
				TransactionOriginID = 4 OR 
				TransactionOriginID = 5 OR 
				TransactionOriginID = 6 
			)
			
			AND Date > '2007-08-01 00:00:00.000' 
			AND Date < '2007-09-01 00:00:00.000' 
		) 
	)
)
GROUP BY TransactionOrigin.Description

Here are the results for the sa login:

A	56456	48732.000
B	78752	456186.0000
C	74	444415.0000
D	2689	554564.00000

Here are the results for the ReportReader login:

A	2       0.58000
C	1       0.1000

Not sure how the query details will assist as I would assume that the problem is to do with permissions given that the code executed is identical?

Many Thanks

 

[TysonLPrice]

I got burned on something that sounds familiar twice recently. Both were my fault, but I got different results when I ran it versus the end user (SQL 2000 though).

1. I had accidentally created an alias in Visual Basic using the debugger that SQL was using when I ran versus the user. Someone here pointed me to checking with cliconfg from the DOS command prompt. That's how I found the alias.

2. I had accidentally left a test versus of my table out there. When the application ran SQL used my login. When the users ran it it used the dbo version.

Silly stuff on my part but it did cause different results when I ran versus the users.