I'm not sure but it always helps if the object owner is DBO instead of a user too. unless you made it as the DBO -----Original Message----- From: Vishakha [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 02, 2000 6:19 AM To: [EMAIL PROTECTED] Subject: Re: MsSQL 7 stored procedure help If your sp is running independently in sql query analyzer. then check for the user permissions in sql Call stored procedure like: <cfquery name="call_keyword_separation" datasource="datasource_name"> {call username.app_SESSION_sp (#cookie.cfid#,#cookie.cftoken#,#themembercookie#) } </cfquery> ----- Original Message ----- From: Peter Janett <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, March 02, 2000 2:10 PM Subject: MsSQL 7 stored procedure help > I can't seem to get the permissions set to allow the user that Cold Fusion > is logging in as to execute a stored procedure. I don't know what else to > try, and since time is not on my side, I thought I'd try to recreate the > functionality without using the stored procedure. > > ANY help most appreciated. > > Here is the CF code that calls it: > <!---------------- > The session stored > procedure - delete old > sessions, updates session > last used, checks for the member > cookie only when the session is initiliazed > returns: > s_session_id > s_session_user (0 = no user) > s_session_number > s_session_quick_buy > The user *will have* a session on every > page the user hits. > ------------------> > <cfquery name="call_session_sp" datasource="database" USERNAME="*******" > PASSWORD="******"> > { call app_SESSION_sp > ( > #cookie.cfid#, > #cookie.cftoken#, > #themembercookie# > ) > } > </cfquery> > > ------------------------------- > Here is the Stored Procedure: > > /****** Object: Stored Procedure dbo.app_session_sp Script Date: 9/24/99 > 7:25:24 PM ******/ > CREATE PROCEDURE app_session_sp > @session_cookie_id int, > @session_cookie_token int, > @member_cookie int > AS > declare @now datetime > select @now = getdate() > declare @time_out datetime > SELECT @TIME_OUT = DATEADD(mi, -25, @now) > declare @user int > select @user = 0 > DECLARE @ACTIVE BIT > DECLARE @quickbuy bit > select @quickbuy = 0 > begin transaction > delete from carts > where cart_frn_session_id IN (select session_id > from sessions > where session_last_used < @time_out) > DELETE FROM SESSIONS > WHERE SESSION_LAST_USED < @TIME_OUT > commit transaction > SELECT @ACTIVE = 0 > > SELECT @ACTIVE = ( SELECT SESSION_ID > FROM SESSIONS > WHERE SESSION_COOKIE_ID = @SESSION_COOKIE_ID > AND SESSION_COOKIE_TOKEN = @SESSION_COOKIE_TOKEN ) > > > IF (@ACTIVE = NULL OR @ACTIVE = 0) > > begin > > if (@member_cookie > 0) > begin > > > select @user = (select user_id > from users > where user_cookie_value = @member_cookie) > if (@user > 0) > begin > select @user = @user > > select @quickbuy = (select user_quick_buy > from users > where user_id = @user) > end > else > begin > select @user = 0 > select @quickbuy = 0 > end > end > > begin transaction > > insert into sessions > ( > session_cookie_id, > session_cookie_token, > session_frn_user_id, > session_last_used, > session_number, > session_quickbuy > ) > values > ( > @session_cookie_id, > @session_cookie_token, > @user, > > getdate(), > 0, > @quickbuy > ) > > declare @IDTOUSE int > select @idTOUSE = @@identity > > commit transaction > > > end > > > ELSE > begin > update sessions > set session_last_used = getdate(), > session_number = ((select session_number > from sessions > where session_cookie_id = @session_cookie_id > and session_cookie_token = @session_cookie_token) + 1) > where session_cookie_id = @session_cookie_id > and session_cookie_token = @session_cookie_token > end > > select s_session_id = (select session_id > from sessions > where session_cookie_id = @session_cookie_id > and session_cookie_token = @session_cookie_token), > s_session_user = (select session_frn_user_id > from sessions > where session_cookie_id = @session_cookie_id > and session_cookie_token = @session_cookie_token), > s_session_number = (select session_number > from sessions > where session_cookie_id = @session_cookie_id > and session_cookie_token = @session_cookie_token), > s_session_quick_buy = (select session_quickbuy > from sessions > where session_cookie_id = @session_cookie_id > and session_cookie_token = @session_cookie_token) > > ------------------------------ > Thanks, > > Peter Janett > > New Media One Web Services > ~Professional results with a personal touch~ > http://www.newmediaone.com > [EMAIL PROTECTED] > (303)915-9019 > > ----- Original Message ----- > From: Peter Janett <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, March 01, 2000 2:17 AM > Subject: Permissions on stored procedures MsSQL 7 > > > > I inherited a cf site and SQL 7 database. The host it came from didn't > use > > usernames or passwords on the database. There is one small stored > procedure > > in the database, which I was able to import to the new server. > > > > My problem is I can't give the user that Cold Fusion connects as > permission > > to execute the stored procedure. On the procedure permission screen, I > > check the exe box under the desired user, click apply, and it seems to go > > through. However, CF can't use the stored procedure, and when I go back > to > > the stored procedure permissions, the exe I just checked is no longer > > checked. It doesn't seem to take. The sql username has all the > permissions > > possible for a user, and is also the owner of the database. > > > > I don't know what else to try. Any help most appreciated. > > > > Thanks, > > > > Peter Janett > > > > New Media One Web Services > > ~Professional results with a personal touch~ > > http://www.newmediaone.com > > [EMAIL PROTECTED] > > (303)915-9019 > > > > -------------------------------------------------------------------------- > ---- > > Archives: http://www.eGroups.com/list/cf-talk > > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > > > > -------------------------------------------------------------------------- ---- > Archives: http://www.eGroups.com/list/cf-talk > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. > ---------------------------------------------------------------------------- -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. ------------------------------------------------------------------------------ Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
