Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Access 2003 ADP runs SQL 2005 SP to selectively delete rows - does not remove any rows Expand / Collapse
Author
Message
Posted Monday, December 3, 2012 4:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 1, 2013 1:18 PM
Points: 3, Visits: 9
Run through the VBA code, SP removes no records.
Run through the database queries window, SP removes no records.
Run the SP through the Management Studio work exactly as designed.
Not a complex SP.
Any thoughts?
Post #1392179
Posted Tuesday, December 4, 2012 6:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:34 PM
Points: 48, Visits: 9,470
Hi

Two possibilities which come to mind - first permissions ie from VBA, the user that is executing the SP does not have appropriate database permissions; second, one or more of the parameters you are supplying the SP is not being interpreted as you expect when executed outside the SQL Server environment - I guess this might be referred to a data typing issue.

For example, if your parameters include a date, you may need to supply the date in a SQL Server format eg '2012-12-25' (including the quotes). A date coming from VBA maybe in the form '25/12/2012' and your SP mayneed to be modified to deal with it in this form (or you change the way in which it is presented as a parameter.

Hope this helps.

Cheers

Rowan
Post #1392757
Posted Wednesday, December 5, 2012 9:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 1, 2013 1:18 PM
Points: 3, Visits: 9
Thanks.
The SP is simply Delete from tablename where codefield = 'C'
I've thought it was permissions all along, but I can't find any that are not set properly.
The users are all PUBLIC
Public has EXECUTE rights on the SP
Public has ALL rights on the table (Delete Insert Select Update)
Is there anything about launching it from Access-VBA I should know?
BTW: All my other SPs (non-delete) run fine launched from Access ADP.
Post #1393069
Posted Wednesday, December 5, 2012 3:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:34 PM
Points: 48, Visits: 9,470
There is a big difference between you running an sp from Management Studio and the users running it from Access. When you test from Management Studio you are doing so with all your admin rights. When a user executes the sp, it is the rights assigned to that specific user login.

While I am not used to using ADPs (I stick to developing in MDB/MDE dbs, normally using ODBC) I don't imagine the rules change when it comes to communicating with SQL Server and getting through its security.

When it comes to deletes, users require different permissions (when compared to select or update) so I would test by giving the users the Sysadmin server role. If your delete works then you have narrowed the solution to security and permissions by eliminating any question about anything further upstream ie the syntax of the exec being sent from VBA.

If you still can't resolve the issue from there, provide us with some details on how you have you secuirty set up, particularly whether or not you are using SQL Server or Windows Authentication.

My normal method is to use Authentication via an AD group to which I assign my users. I then create a SQL Server login for the group. The application and environment will determine how I assign permissions from there. I think you will find that this is a widely adopted method. Often I will develop a permissions script setting permissions for the varous objects.

Cheers

PS - Another thought - check the permissions section of the DELETE(Transact-SQL) explanation in BOL.

PPS - Running from VBA, you are going to be creating an SQL string which you are then executing? There is something about this in the back of my mind somewhere. I'll try to get on to Google today - it may trigger something.
Post #1393249
Posted Thursday, December 6, 2012 1:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 10:42 AM
Points: 103, Visits: 811
"Often I will develop a permissions script setting permissions for the varous objects."
What do you use to develop permission scripts?
Post #1393360
Posted Thursday, December 6, 2012 2:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:34 PM
Points: 48, Visits: 9,470
Sorry, I should have been more specific.

I write/re-use a T-SQL script eg
grant execute on sp_SomeName to A_App_SecurityGroup

If the database only has a small number of objects I'll often just write a script including all the objects and the relevant permissions which I wish to provide. For larger databases I'll use the built-in system views such as sys.objects to provide a list of objects of a specific type.

These scripts can be as simple or as complex as you wish. Using the system views is a great way to build a script which can be re-used - particularly useful if your database is still maturing.

If you google for examples, you'll find hundreds. But if you do a search here on SQLServerCentral and specify the scripts option I think you'll find plenty of food for thought.

If you can't find anything suitable let me know and I'll dig one out for you.

Cheers
Post #1393388
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse