delete a record

Before I get into my question let me give you a little background on me first. I am a ASP programmer and I know enough about SQL Server (user side) to manipulate records to check my screens.

As the title suggests, I am not able to delete a record from an sql table and I was able to about 2 months ago. About 6 months ago the DBA had to make some setting changes on my machine and it was all good. The last 2 months or so, I worked on another project. Then about 3 days ago I went to delete a record and it wouldn't let me. The only thing I can imagine is 2 months ago when I had to have someone reset my user profile on my machine, it overrode whatever it was that the DBA had done.

Well you might be saying "well get the DBA back down to fix it." He took a promotion to another agency 3 months ago and the new guy couldn't help me. In his defense, he is an interim until they can hire an experienced DBA. Sorry for being so long winded. But if anyone could point me in the right direction, I would appreciate it.

There were no deny statements when I ran that stored procedure. They were all granted.

John 2010-10-08 13:19:52

@John, are you sure you ran this correctly? According to you error message there should have been some information. Make sure the query tab in SSMS is opened to the correct database (or type USE cbcweb) then try EXEC SP_HELPROTECT 'tcbcteprep'. That should bring up some rows of data. Next you will need to get someone with db_owner rights to execute GRANT DELETE ON dbo.tcbcteprep TO <your role or login>

Noel Abrahams 2010-10-08 13:56:14

This is what I get when I run sp_helpprotect on tcbcteprep:

John 2010-10-08 14:06:22

Msg 15302, Level 11, State 1, Procedure sp_helprotect, Line 87Database_Name should not be used to qualify owner.object for the parameter into this procedure.

John 2010-10-08 14:06:55

The error message seems quite self-explanatory.

Noel Abrahams 2010-10-08 15:57:51

and yet it's not since our interim DBA still hasn't been able to fix it. He thinks it has something to do with my login to the network superceding the rights of the server/database.

John 2010-10-08 18:20:50

Chalk this one up to being a novice. lol I was using Enterprise Manager all along and I couldn't delete the record. For gee whiz, I tried using Query Analyser and what do you know, it worked. So after finding out which login I was using on Query Analyser, I deleted and re added the database in Enterprise Manager using the same login as Query Analyer and it's all good now. Thanks for all of your help.

John 2010-10-13 15:48:14

Phew, I can go get some sleep now ;-)

Noel Abrahams 2010-10-13 16:14:01

[microsoft][odbc sql server driver][sql server]DELETE permission denied on object 'tcbcteprep',database 'cbcweb',owner 'dbo'

John 2010-10-08 13:16:15

Thanks - in that case, you will need to get someone who has SA rights on the server to give you delete permissions on the tcbcteprep table. For whatever reason, your privileges are no longer sufficient.

JasonA 2010-10-08 13:50:12

well that was checked yesterday and the userid/login that i have on that database has read/write access. Last time he did something on my machine that enabled me to delete. Could it have anything to do with how I login to the network in the morning? Sorry I am not much help.

John 2010-10-08 13:54:06

No worries! Yes, since you mentioned you had to reset your user profile, that's probably what happened. He probably came down, logged you in as a specific SQL user account, and saved the credentials. Now, you're probably using integrated security, and it's passing through your network credentials, and that account doesn't have access explicitly. You might try emailing the original DBA and asking him if there was a specific user name and password he used for you previously?

JasonA 2010-10-08 14:01:02

Thanks Jason. I actually emailed him shortly after receiving your previous comment. Just waiting to hear back from him now.

John 2010-10-08 14:08:31

still no word but will keep you posted. Thanks again.

John 2010-10-08 18:21:45

John 2010-10-13 15:48:39

ansaurus

tags:

views:

answers:

related questions