January 2, 2009 at 10:36 am
I have some data in a temptable at one sql server. I have to transfer it to a permanent table in another.
Both are linked. Linked user user lnkUsr.
I gave Insert permission for lnkUsr on the permanent table.
When I try to Insert data like
INSERT [LinkedServer].dbname.dbo.Permanant Table
SELECT * FROM #Table
I am getting an error
Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object
Can experts help either teaching me how to grant permissions to everyone on the permanent table or any other means.
Regards,
gova
January 2, 2009 at 12:11 pm
You should probably start by taking a look at what security mappings were set up in the linked server object. If everyone is getting that message, then it is likely that you are mapping everyone to a single remote login which doesn't have permissions. You may need to try some of the other options there to see which ones work for you.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 2, 2009 at 4:34 pm
You can use the DataReader DB role. Just add the RemoteLogin's user in the database to that role.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 3, 2009 at 3:48 am
THank you for the replies.
Linked server properties shows it is using lnkUsr with password. I gave
GRANT ALL ON permanaentTable to LnkUsr.
It did not work.
I made the lnkUsr as sysadmin temperarily it works then.
Regards,
gova
January 3, 2009 at 1:27 pm
you can try granting that login public access to the DB, and then seeing if that works...
January 3, 2009 at 1:46 pm
Thanks SK I can change the access rights temperarily to check. Any chnage on user rights would apply to all the objects. We don't want that.
But I own only the table. When I grant the lnkuser all the rights on the table why is it not working. It is working okay with linked SQL Server version 2000.
Regards,
gova
January 3, 2009 at 4:06 pm
Can the user run the statement without going through the linked server? That will help rule out any issues with the linked server permissions.
If not, I would try putting that user into dbo on the database that the table lives in, then try again. if that works, I would then scale back access in a "reverse" manner...
Best of luck,
SK
January 4, 2009 at 6:14 am
Thank You.
SK (1/3/2009)
Can the user run the statement without going through the linked server? That will help rule out any issues with the linked server permissions.If not, I would try putting that user into dbo on the database that the table lives in, then try again. if that works, I would then scale back access in a "reverse" manner...
Great. Good point. Why didn't I think about this. I will do it first thing in the Monday morning. (EST -US)
Regards,
gova
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply