[dba-SQLServer] Field missing in Linked SQL Table/View

[David Emerson]

Stuart - This helped solve the problem.  The fields had user defined types.
The developer needed to assign the correct permissions for the types.  This
allowed the fields based on the types to be visible in the linked tables in
access.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand


-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On
Behalf Of Stuart McLachlan
Sent: Thursday, 14 January 2016 9:29 p.m.
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Field missing in Linked SQL Table/View

Yes.

https://technet.microsoft.com/en-us/library/ms180341%28v=sql.105%29.aspx

and more detail:

https://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-usi
ng-column-level-
permissions/



On 14 Jan 2016 at 9:02, David Emerson wrote:

> Hi Paul,
> 
> Can permissions be set on a field?  I have no problem linking to the 
> table.
> 
> Regards
> 
> David
> 
> -----Original Message-----
> From: dba-SQLServer
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul 
> Hartland Sent: Thursday, 14 January 2016 8:32 a.m. To: Discussion 
> concerning MS SQL Server Subject: Re: [dba-SQLServer] Field missing in 
> Linked SQL Table/View
> 
> Just off hand it sounds like a permissions thing, do you use the same 
> logon to run the view in SQL server as you are using for the linked 
> tables to Access ?
> 
> On 13 January 2016 at 18:40, David Emerson <newsgrps at dalyn.co.nz>
> wrote:
> 
> > Hi Listers,
> >
> > I have an SQL 2012 database that belongs to another application 
> > which I have little control over.  I am allowed to create views of 
> > the tables.  I have created a view of a particular table which just 
> > selects a few of the fields from the table.  I have then linked the 
> > view to my Access 2010 database.
> >
> > The unusual thing is that one of the fields does not appear in the 
> > linked table in Access.  It is definitely in the view and appears 
> > when I run the view.
> >
> > I have tried linking directly to the SQL table and this includes all 
> > the fields except for the same field missing from the view.
> >
> > I have changed the view and relinked it - all of the changes show in 
> > the linked table except the same field doesn't show.
> >
> > There is nothing unusual I can see about the field.  It is a 
> > nvarchar(25). The type is a user defined data type but so are a 
> > number of other fields that all appear in the linked table.
> >
> > Has anyone come across this before?
> >
> > Regards
> >
> > David Emerson
> > Dalyn Software Ltd
> > Wellington, New Zealand