SQL Server Stored Procedure Authentication

[airnewhouse]

Hello,

I am tyring to add a level of security to my application with using a username
and password to authenticate to the database with when using CFStoredProc to
call a SQL Server 2005 Express Stored Procedure. Right now the calls to the
stored procedures work fine if I don't include the database connection
information. They will also work if I use the "SA" connection information for
all instances of the SQL Server 2005 Express Server. I cannot get the stored
procedure calls to work when I use just the database owner of the individual
instance/database of this application though. I think this would be the ideal
way to do it security-wise because I am setting this connection information in
my application.cfm file and if that file were to ever be compromised, then they
would only be able to access the instance/database of this application instead
of all the instances contained on the whole SQL Server 2005 Express Server.
Make sense?? Below is the code in my application.cfm and a stored procedure
call if that helps as well.

<!--- setting stored procedure connection info in application.cfm --->
<CFSET application.username = "user1">
<CFSET application.password = "password">

<!--- STORED PROCEDURE CALL --->
<cfstoredproc datasource="#application.dsn#" username="#application.username#"
password="#application.password#" procedure="sp_GetLocations">
<cfprocresult name="GetLocations">
</cfstoredproc>

The error I get is "Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'user1'"

I am pretty sure that the user is setup correctly on the SQL Server side as
well. I used the sp_grantdbaccess stored procedure and the user showed up in
the list of users for the database. Is there something else that I need to do
on the SQL Server side? Perhpas make that user the dbowner or something??

Thanks in advance,
airnewhouse

[airnewhouse]

OK, I gave the user the db_owner role, but that didn't seem to help either....

[philh]

Can you log into Query Analyzer as user1 and run the SP? Does the user have explicit permissions to the SP? You may have to give explicit permissions for it to work.