permission - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

permission

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-12-20 : 09:49:42

i grant perrmission to user on table just for select, when he try to get it with linke server he get this error:

The OLE DB provider "SQLNCLI" for linked server "SERV1" does not contain the table ""DBName1"."Sechema1"."TableName1"". The table either does not exist or the current user does not have permissions on that table.

on the other tabke like TableName2 it is work,what can i do?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-21 : 10:52:40

what security options did you choose for the linked server?

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-12-22 : 01:42:02

Be made using this security context:
Remote login:NewUer
with password:
(i create a new login (NewUser) for that, i see when i give to NewUser permission,and after that i give to user1 same permission,user1 can see the table,if i didnt give permission to NewUser so user1 cant see the table)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-22 : 10:39:14

you just need to give permission to NewUser

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-12-22 : 15:53:31

why i need the sql user (NewUser) for the link server?
the user1 is from active dirctory

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-23 : 11:26:17

you don't but you set up the linked server to use that account.

if you want to use windows authentication, then you'll need to
1. Register the SPN for the SQL Server service account on the remote server (need to be domain admin to do this)
2. Allow delegation for that account in AD
3. Give domain user(s) permissions on remote (linked) server
4. Change linked server security context to "Be made using the logins current security context"

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-12-27 : 03:31:37

if you mean who create the linke server need to be Domain Admin,
so i am not Domain Admin.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-28 : 09:53:42

no. i mean who registers the SPN

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-12-29 : 06:25:56