Post reply

Can't Grant Delete

  • November 19, 2012 at 7:22 am

    #267068

    Hello,

    I am having a problem that is driving me crazy.

    3 weeks ago, I moved a bunch of Databases from a 2005 Instance

    to a 2008 R2 Instance. All went well except for a problem deleting on

    one of the databases.

    There are many jobs that run each morning. One job that purges

    and repopulates a table keeps returning...

    The DELETE permission was denied on the object 'MyTable', database 'MyDb', schema 'MySchema'.

    Permissions are all the same.

    I even went back and granted permission on the table and schema.

    Any help on the steps I need to take to find and fix this problem would be very much appreciated.

    thanks in advance,

    Bob.

  • November 19, 2012 at 8:21 am

    #1560390

    See if you can drop and recreate the user, maybe there's a disconnect there somewhere?

    Have you tried logging into SQL Serevr as that user and run sme DELETE statements in SSMS? (possibly create a SP with EXECUTE AS thatuser).

    B

  • November 19, 2012 at 8:44 am

    #1560404

    Thanks B.

    The user would be myself. I created the Job.

    The job fires a stored proc that has other SP's..

    one of the SPs does the purgepop.

    If I execute the SP From SSMS (BY ITSELF) It works fine.

    If It executes from within the other SP, it returns that Delete permission was denied.

    so...

    EXEC MyProc2 works fine....

    Exec JobProc...

    (which has...

    exec MyProc1

    exec MyProc2.. etc...

    )

    returns the Delete Permission denied.

    thanks again for the reply,

    ..bob

  • November 19, 2012 at 10:16 am

    #1560440

    Hi Bob,

    It sounds like the problem could be connected to the mapping between the DB user and the login, have you tried "ALTER USER [your user name] with LOGIN = '[your user name]"?

    MCITP
    MCTS - E-Business Card
    Twitter: WWDMark

    Try not! Do or do not, there is no try

    email: info@weekendwebdesign.co.uk
    Personal Website: http://markallen.co.uk/
    Business Website: https://www.weekendwebdesign.co.uk

  • November 19, 2012 at 10:39 am

    #1560452

    Create another account and grant it the ability to delete see if that works.

    Then try and sort out the permission issue with your account if that works.

  • November 19, 2012 at 10:52 am

    #1560463

    Bob McClellan-320407 (11/19/2012)

    Thanks B.

    The user would be myself. I created the Job.

    The job fires a stored proc that has other SP's..

    one of the SPs does the purgepop.

    If I execute the SP From SSMS (BY ITSELF) It works fine.

    If It executes from within the other SP, it returns that Delete permission was denied.

    so...

    EXEC MyProc2 works fine....

    Exec JobProc...

    (which has...

    exec MyProc1

    exec MyProc2.. etc...

    )

    returns the Delete Permission denied.

    thanks again for the reply,

    ..bob

    Silly idea, but since these run as a job, perhaps the RUN AS user is something other than you for one of the steps?

  • November 19, 2012 at 11:48 am

    #1560478

    Thanks Everyone....

    Dropping and ReCreating myself as a user, in the db having the problem...

    seems to have fixed it.

    I'd like to understand more about what exactly went south with that user login but...

    for now I am working.

    Thanks again.

    Very much appreciated!

    ..bob

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

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