Access 2003 ADP runs SQL 2005 SP to selectively delete rows - does not remove any rows

Question

Access 2003 ADP runs SQL 2005 SP to selectively delete rows - does not remove any rows

mwolfstone

SSC Rookie

Points: 27
More actions
December 3, 2012 at 4:31 pm

#74746

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ProofOfLife Ten Centuries Points: 1332 More actions · Answer 1

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

mwolfstone SSC Rookie Points: 27 More actions · Answer 2

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.

ProofOfLife Ten Centuries Points: 1332 More actions · Answer 3

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.

grovelli-262555 SSCrazy Points: 2633 More actions · Answer 4

"Often I will develop a permissions script setting permissions for the varous objects."

What do you use to develop permission scripts?

ProofOfLife Ten Centuries Points: 1332 More actions · Answer 5

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