Link to home
Start Free TrialLog in
Avatar of dbaSQL
dbaSQLFlag 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?
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

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

exec [LinkedServer].msdb.dbo.sp_send_dbmail

Open in new window

Avatar of 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.

Use the full name in order to identify the object:

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

ASKER

I am already doing exactly that.

EXEC SERVERNAME.DATABASENAME.DBO.PROCEDURENAME
Grant the account with a sysadmin role, just for debugging. Try again, please.
Avatar of 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?

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

Create a temporary table, insert something, and discconnect.

Avatar of Racim BOUDJAKDJI
Ask your Active Directory people to activate Kerberos.
Avatar of dbaSQL

ASKER

racimo, can you elaborate, please?  
ASKER CERTIFIED SOLUTION
Avatar of Racim BOUDJAKDJI
Racim BOUDJAKDJI
Flag of Algeria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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.

Avatar of dbaSQL

ASKER

Though they said kerberos is enabled, it looks like this is the problem.  Thank you, racimo.
<<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.
Avatar of dbaSQL

ASKER

Got it.  I will let you know what I find.
Avatar of dbaSQL

ASKER

and thank you again, racimo
Hope this works...
Avatar of dbaSQL

ASKER

so do i  

:-)