jwcolby
jwcolby at colbyconsulting.com
Sat Jun 18 16:06:13 CDT 2011
That was it. I never used rights to objects such as views and SPs. The database if used by community volunteers (me) to fill out the paperwork to check minimum security prisoners out of prison to take to AA meetings, church and other such innocuous places. I am looking to expand the database beyond the camp in my area to allow CVs all over the state to use the database. It is an Access FE coming into a SQl Server database on my servers (a VM used for only that) over the internet. It is working quite well so far, but it is only myself and one other CV at this point. Now I am trying to figure out how to handle locations (places a prisoner is allowed to go) and AA meetings around the entire state and yet somehow group them by the camp they are close to. A CV has a card to check out prisoners for a specific camp, and can enter their own churches, AA meetings, restaurants etc where they will be taking the prisoners. Obviously I could just put a camp id in the location table but then a location close to two different camps would be in there twice (and have to be entered twice). Or i could have a camp location m-m for entering the campid and location id to "pair" the location to a camp. That would be more efficient but perhaps hard for the average user to fathom how to enter. Anyway, I am expecting the data to grow enormously as users come on around the state and I need to use SPs to pull only data for camps the CV visits, particularly things like combos of locations, meetings, inmates etc. Until yesterday I had never used a select SP from Access John W. Colby www.ColbyConsulting.com On 6/18/2011 1:31 PM, Francisco Tapia wrote: > I'd check that too, it seems that maybe your user has data reader > access which gives full select rights to tables and views but not exec > rights to sprocs. > > Inmate checkout eh? Maybe lock that connection down beyond data > reader access too. I'm just saying. > > Sent from my mobile > > On Jun 17, 2011, at 8:01 PM, Stuart McLachlan<stuart at lexacorp.com.pg> wrote: > >> Have you specifically set Execute permissions on the SP for MyUser? >> >> -- >> Stuart >> >> On 17 Jun 2011 at 22:50, jwcolby wrote: >> >>> I am trying to figure out how to use stored procedures to return >>> recordsets to Access. I created a stored procedure that accepts a >>> parameter, the stored procedure pulls a recordset and if used like so: >>> >>> exec usp_MySP 1 >>> >>> returns a recordset to the query window in SQL Server. >>> >>> So, now how to get it to work in Access. I thought I was going to >>> create a pass through query dynamically, save it and then "open" the >>> query to get the recordset. >>> >>> The query is a passthrough query >>> >>> usp_InmatesForVolunteers 1 >>> >>> The ODBC connect string is: >>> >>> ODBC;DRIVER=SQL >>> Server;UID=MyUser;PWD=MyPassword;SERVER=5.58.170.179;DATABASE=InmateCh >>> eckout; >>> >>> All of this (except for the database part) comes directly out of a DSN >>> file which works to link tables and views into Access. Tested and >>> working for that purpose. >>> >>> So the passthrough query fails. Error: >>> >>> [Microsoft][ODBC Sql Server Driver][SQL Server]The execute permission >>> was denied on the object 'usp_InmatesForVolunteers', database >>> 'InmateCheckout', schema 'dbo'. (#229) >>> >>> Interestingly if I remove the database part of the connection string >>> it returns exactly the same error message, even mentioning that >>> database. Thus the user itself being mapped to that database seems to >>> be working as well. >>> >>> As I said, this all works for linking SQL Server tables and views, >>> just not executing the sp. >>> >>> Any ideas? >>> >>> -- >>> John W. Colby >>> www.ColbyConsulting.com >>> _______________________________________________ >>> dba-SQLServer mailing list >>> dba-SQLServer at databaseadvisors.com >>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>> http://www.databaseadvisors.com >>> >>> >> >> >> >> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >