The INSERT permission was denied on the object

Question

Post reply

The INSERT permission was denied on the object

Viewing 12 posts - 16 through 26 (of 26 total)

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

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 1

Steve-3_5_7_9

SSChampion

Points: 10832

June 21, 2010 at 6:29 am

#1182684

... and the security settings of the link itself.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 2

GilaMonster (6/21/2010)
striker-baba (6/21/2010)
And I am trying to insert data through a linked server from a remote test server into my prod server.
It would have been useful if you'd mentioned that in your initial post. As soon as you're working with a linked server, your permissions on the server are irrelevant, what's important is the security settings on the linked server

I have to echo the same sentiment. Knowing that this was a linked server in the beginning could have saved time.

Verify your link setup. Verify that you have adequate permissions on both servers.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

Script out the linked server or take a screen shot of the security tab of the linked server property dialog and post it for us to see.

Also, are you using Windows account or SQL Auth. logins?

Steve, "the link" is the linked server. A "linked server" is an object on the source server.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

striker-baba SSCrazy Points: 2170 More actions · Answer 4

Even though I try to insert the data manually, I am not able to insert.

So, I think its not the problem with the linkedserver.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

By manually do you mean:

using an insert statement across the linked server?

using an insert connected directly to the server that contains the table?

something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

striker-baba SSCrazy Points: 2170 More actions · Answer 6

By directly connecting to the server.

and manually inserting the data using

insert into cc()

values()

Robert Davis One Orange Chip Points: 28027 More actions · Answer 7

What is the output of the following when connected directly to the server/database?

Select *

from fn_my_permissions ( '<Table Name>' , 'object')

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Lempster SSCoach Points: 15651 More actions · Answer 8

Just a thought - is the database read-only?

Open a new query window against the master database and execute this:

SELECT DATABASEPROPERTYEX('the_name_of_your_database','Updateability')

In order for you to be able to run UPDATE, DELETE or INSERT statements against the database it has to be in READ_WRITE mode.

Regards

Lempster

grbihno SSC Journeyman Points: 86 More actions · Answer 9

Hi.

I've had the same problem.

After some poking I realized that Windows group of this user was also a user in a database and was denied write access via db_denydatawriter role. Removed group user from the role and that was it.

jahid786 SSC Enthusiast Points: 185 More actions · Answer 10

I have the similar issue. I have checked all the suggestion you made on top of this discussion. But I could not find the reason of denying the insert or update.

user granted db_owner. but user cannot insert or udpate the data. only the server admin role are allowed to insert into.

it seems the issue has happened recently.

can any one help me here?

Lowell SSC Guru Points: 323482 More actions · Answer 11

jahid786 (2/6/2015)
I have the similar issue. I have checked all the suggestion you made on top of this discussion. But I could not find the reason of denying the insert or update.
user granted db_owner. but user cannot insert or udpate the data. only the server admin role are allowed to insert into.
it seems the issue has happened recently.
can any one help me here?

you might want to start a new thread to get more results, but in your case, assuming you are db_owner, i'd look first to at any triggers that might be entering data into another database for auditing purposes;

in that case, db_owner rights would not transfer across to another db.

are you having the same issue, as the OP with a linked server issue? in his case he was superman locally, and a normal user over the linked server, could that be what your issue is?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jahid786 SSC Enthusiast Points: 185 More actions · Answer 12

No linked server been used. The login has not been used for any trigger in any database. Also I noticed that except the server adminitrator group member can write or update into the database. no one else can update or insert into the database.