Avatar of dbaSQL
dbaSQL
Flag for United States of America asked on

db_datawriter, linked server definition

I have a linked server login on all of my machines.  The associated database user has is a member of db_datareader and db_datawriter within each user database.   The user has also been granted EXECUTE, to be able to exec all procs.

the linked servers are each running under the context of this user

i am executing a proc remotely, and continually failing w/this:
Msg 1088, Level 16, State 7, Procedure procedurename, Line 19
Cannot find the object "tablename" because it does not exist or you do not have permissions.

the procedure does an insert into a table.  the user is a member of db_datawriter.

i have used this linked server definition a gazillion times.  i can't determine the source of this problem.
the table is definitely there.  logged in locally, the proc runs just fine

anybody?
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
dbaSQL

8/22/2022 - Mon
dbaSQL

ASKER
I should note that it is v2005.  I've used this definition many, many times successfully, with v2008.  
jorgedeoliveiraborges

Example:

exec [LinkedServer].msdb.dbo.sp_send_dbmail

Open in new window

dbaSQL

ASKER
Not sure what you are suggesting.  I know how to run it, it is just not receiving privilege to write into the table.  Yet, the user is a member of the db_datawriter role.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
jorgedeoliveiraborges


Use the full name in order to identify the object:

1. database.schema.storedprocedure, or
2. llinkedserver.database.schema.storedprocedure, please.
dbaSQL

ASKER
I am already doing exactly that.

EXEC SERVERNAME.DATABASENAME.DBO.PROCEDURENAME
jorgedeoliveiraborges

Grant the account with a sysadmin role, just for debugging. Try again, please.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dbaSQL

ASKER
As I said, I have been using this linked server definition for quite some time, successfully.  I have never had to do the sysadmin.  Doing so now would basically open the door completely, on the linked server traffic.

The login is a member of db_datawriter -- why can it not write to the table?

jorgedeoliveiraborges

>> why can it not write to the table?
I wish I knew.

Create a temporary table, insert something, and discconnect.

Racim BOUDJAKDJI

Ask your Active Directory people to activate Kerberos.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
dbaSQL

ASKER
racimo, can you elaborate, please?  
ASKER CERTIFIED SOLUTION
Racim BOUDJAKDJI

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dbaSQL

ASKER
Yep. That is the problem.  I have run the verification statement referenced in the second article that your link references (see below), and it tells me we're doing NTLM.
http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx

I'm not an Active Directory person.  

He says he'll take a look at it tomorrow.  Hopefully the 'using kerberos with sql server' is as simple as that article suggests.

Thank you, Racimo.  I'll award and close, but tomorrow I'll let you know how things progress.

dbaSQL

ASKER
Though they said kerberos is enabled, it looks like this is the problem.  Thank you, racimo.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Racim BOUDJAKDJI

<<Though they said kerberos is enabled, it looks like this is the problem>>
Double check.  I can not count the number of AD people whom I had to ask to double verify that certain settings were effectively active.
dbaSQL

ASKER
Got it.  I will let you know what I find.
dbaSQL

ASKER
and thank you again, racimo
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Racim BOUDJAKDJI

Hope this works...
dbaSQL

ASKER
so do i  

:-)