Can't Grant Delete

Question

Can't Grant Delete

BobMcClellan

SSC-Addicted

Points: 400
More actions
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.

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

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

bleroy Hall of Fame Points: 3052 More actions · Answer 1

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

BobMcClellan SSC-Addicted Points: 400 More actions · Answer 2

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

WWDMark Hall of Fame Points: 3157 More actions · Answer 3

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

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 4

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.

DiverKas SSCrazy Points: 2049 More actions · Answer 5

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?

BobMcClellan SSC-Addicted Points: 400 More actions · Answer 6

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