Issue with Linked server permissions

• Pyman

  SSChampion

  Points: 13830

  More actions

  August 14, 2012 at 11:13 am

  #265088

  Hi All,

  I am trying to set identity off over a linked server on a table. Both the servers are SQL Servers. The login is a domain admin account and has sys admin permissions on both servers.

  I am using the following in the stored proc:

  set IDENTITY_INSERT [linkserver].[database].[dbo]. off

  and the error I am getting is:

  Cannot find the object "linkserver.database.dbo.table" because it does not exist or you do not have permissions.

  But as said the account I am using is a domain admin account with sysadmin permissions on both sql servers.

  I have selected dynamic parameters, nested queries, level zero only, allow in process in the MSDASQL properties.

  Thanks in advance for your inputs .

  “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

• Jared

  SSC Guru

  Points: 61793

  More actions

  August 14, 2012 at 11:41 am

  #1525696

  Sapen (8/14/2012)

  ---

  Hi All,

  I am trying to set identity off over a linked server on a table. Both the servers are SQL Servers. The login is a domain admin account and has sys admin permissions on both servers.

  I am using the following in the stored proc:

  set IDENTITY_INSERT [linkserver].[database].[dbo]. off

  and the error I am getting is:

  Cannot find the object "linkserver.database.dbo.table" because it does not exist or you do not have permissions.

  But as said the account I am using is a domain admin account with sysadmin permissions on both sql servers.

  I have selected dynamic parameters, nested queries, level zero only, allow in process in the MSDASQL properties.

  Thanks in advance for your inputs .

  Can you query from that table? My suspicion is the security setting on the linked server. How are you authenticating in the linked server?

  Jared
  CE - Microsoft

• Lowell

  SSC Guru

  Points: 323546

  More actions

  August 14, 2012 at 11:46 am

  #1525699

  i think only simple DML queries will work on 4 part names;

  you have to issue commands like that with EXECUTE.. AT for commands, SET or DBCC options.

  EXECUTE ( 'set IDENTITY_INSERT [database].[dbo]. off; ' ) AT [linkserver];

  that's what i've had to do for DDL, like CREATE TABLE / CREATE INDEX and a bunch of other commands .

  Sapen (8/14/2012)

  ---

  Hi All,

  I am trying to set identity off over a linked server on a table. Both the servers are SQL Servers. The login is a domain admin account and has sys admin permissions on both servers.

  I am using the following in the stored proc:

  set IDENTITY_INSERT [linkserver].[database].[dbo]. off

  and the error I am getting is:

  Cannot find the object "linkserver.database.dbo.table" because it does not exist or you do not have permissions.

  But as said the account I am using is a domain admin account with sysadmin permissions on both sql servers.

  I have selected dynamic parameters, nested queries, level zero only, allow in process in the MSDASQL properties.

  Thanks in advance for your inputs .

  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!

• Pyman

  SSChampion

  Points: 13830

  More actions

  August 14, 2012 at 11:58 am

  #1525704

  SQLKnowItAll (8/14/2012)

  ---

  Sapen (8/14/2012)

  ---

  Hi All,

  I am trying to set identity off over a linked server on a table. Both the servers are SQL Servers. The login is a domain admin account and has sys admin permissions on both servers.

  I am using the following in the stored proc:

  set IDENTITY_INSERT [linkserver].[database].[dbo]. off

  and the error I am getting is:

  Cannot find the object "linkserver.database.dbo.table" because it does not exist or you do not have permissions.

  But as said the account I am using is a domain admin account with sysadmin permissions on both sql servers.

  I have selected dynamic parameters, nested queries, level zero only, allow in process in the MSDASQL properties.

  Thanks in advance for your inputs .

  Can you query from that table? My suspicion is the security setting on the linked server. How are you authenticating in the linked server?[/quote

  Yes I am able to select data.

  “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

• Pyman

  SSChampion

  Points: 13830

  More actions

  August 14, 2012 at 12:00 pm

  #1525707

  Lowell (8/14/2012)

  ---

  i think only simple DML queries will work on 4 part names;

  you have to issue commands like that with EXECUTE.. AT for commands, SET or DBCC options.

  EXECUTE ( 'set IDENTITY_INSERT [database].[dbo]. off; ' ) AT [linkserver];

  that's what i've had to do for DDL, like CREATE TABLE / CREATE INDEX and a bunch of other commands .

  Sapen (8/14/2012)

  ---

  Hi All,

  I am trying to set identity off over a linked server on a table. Both the servers are SQL Servers. The login is a domain admin account and has sys admin permissions on both servers.

  I am using the following in the stored proc:

  set IDENTITY_INSERT [linkserver].[database].[dbo]. off

  and the error I am getting is:

  Cannot find the object "linkserver.database.dbo.table" because it does not exist or you do not have permissions.

  But as said the account I am using is a domain admin account with sysadmin permissions on both sql servers.

  I have selected dynamic parameters, nested queries, level zero only, allow in process in the MSDASQL properties.

  Thanks in advance for your inputs .

  Cool... That works ...thanks

  “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams