SQL Permission Problem

I have having an issue with a SQL execution where I am getting an error message

-2147217843 Login Failed for user.

I am able to successfully open the connection to the database and execute select count(*) queries.

I am getting this error when I include fields.

In a separate application that uses the same fields I am able to retrieve the same data so seems to rule out column permissions.

The query coming back with no error is:-

SELECT tbl_PersonalDetails.SystemID 
FROM tbl_PersonalDetails 
WHERE tbl_PersonalDetails.Title IS NOT NULL  
And tbl_PersonalDetails.HospitalNumber IS NOT NULL   
AND tbl_PersonalDetails.SiteID = 1  

The query coming back with the error is:-

SELECT DISTINCT tbl_PersonalDetails.Title,tbl_PersonalDetails.HospitalNumber 
FROM  tbl_PersonalDetails  
WHERE tbl_PersonalDetails.SiteID = 1   
ORDER BY tbl_PersonalDetails.Title,tbl_PersonalDetails.HospitalNumber ASC

This is not specific to these particular queries, in开发者_开发知识库 the first query where we are just doing a count I always get a count back with no issue, when I try to request fields such as in the second I always get the Login Error.

Your problem isn't in the SQL queries you've posted. They would either all fail or all succeed based on the information given.

Your problem is your calling/client code. Sounds like you're using Classic ASP ("recordset....adodb connection").

Double check that your ASP code is using the proper connection strings.

To prove this, run any of these queries in SQL Server Management Studio. Connect using the credentials that your connection string contains.