Re: INSERT Permission & Linked Servers

Craig Wagner (MSDNNospam207@xxxxxxxxxxxxx) writes:
We are in the process of upgrading from SQL2000 to SQL2005. We
discovered one odd behavior in our testing and I've been unable to
determine if this is by design.

We have two servers (A and B). A table (X) exists in a database on
server A. A stored procedure on server B is executed that inserts data
into table X. The account executing the stored procedure has been
granted INSERT to table X.

In SQL2000 this worked just fine. In SQL2005 it generates an error:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object ""Sample"."dbo"."sampletable"". The OLE DB
provider "SQLNCLI" for linked server "sample_svr" indicates that either
the object has no columns or the current user does not have permissions
on that object.

If I grant SELECT permission on the table then the INSERT works.

I would suspecct that this is due to change in how metadata is exposed.
In SQL 2000 everyone has access to metadata. In SQL 2005 you can only
see metadata for an object if you have the permission VIEW DEFINITION
on that object. This permission is implied if you have SELECT permission,
but it appears that INSERT does not imply this permission.

So try granting VIEW DEFINITION rather than INSERT and see if that works
better.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Long delay before Drives & Files appear in My Computer & Address Bar
    ... > SERVICE JEFF-DZP8HX39G2 The machine-default permission settings do not ... > grant Local Activation permission for the COM Server application with ... > Ratcliff JEFF-DZP8HX39G2 The Adobe LM Service service was successfully ... > within the required timeout. ...
    (microsoft.public.windowsxp.help_and_support)
  • Re: SQL Server execute permission denied on object
    ... This is a permission problem: the Login account or the required permissions ... have not been set up properly on the remote database. ... Recently the SQL part was moved to a new server. ... SQL Server execute permission denied on object ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Network shares cannot connect
    ... After a reboot, domain users cannot access any shares on the SBS server, ... Microsoft Online Newsgroup Support ... let's focus on the Users Shared Folder first. ... To check this permission, please click the Advanced button, ...
    (microsoft.public.windows.server.sbs)
  • Re: Export to Excel
    ... maybe your dts-script has no permission to the file/directory ... server through Enterprise Manager. ... package from SQL server. ...
    (microsoft.public.sqlserver.dts)
  • Re: System.Security.SecurityException was unhandled
    ... Is it a must that I need to register the COM server to the machine using ... CAS permissions on the client machine, and the COM issue is a new problem. ... assembly actually has the permission in question. ... When I execute the application I received and error message. ...
    (microsoft.public.dotnet.security)