"Morten Mikkelsen" <mbmnewsreader_@_mikkelsens.net> wrote in message
news:437e4f40$0$47014$edfadb0f@dread15.news.tele.dk...
> Hi,
> On my SQL Server 2000, I have a table of data (tblAllData) containing a
> number of columns, some of which are 'secret'.
> I have to let some users access the database using ODBC from an Excel
> sheet, and I would like that they do not know at all that the columns
> exist.
> I tried creating a view for them (qryAllData) that only selects the
> columns that should be visible, but when the creating the ODBC-connection,
> both the query and the underlying table shows up.
> If I select the table as datasource, the query-builder in excel shows a
> list of all the columns, including the secret ones. If I try selecting
> then, of course, an error occurs.
>
> I would like either that the columns for the table don't show or that the
> table does not show at all - and only reveals the existence of the view to
> the odbc-client.
> Is that possible?
>
> Here's what I tried so far:
>
> <pre>
> USE DbAllData
> sp_addlogin @loginame='ODBCAccess', @passwd='ODBCAccess',
> @defdb='DbAllData'
> sp_grantdbaccess 'ODBCAccess'
> sp_addrolemember @rolename = db_denydatawriter, @membername = ODBCAccess
>
> REVOKE ALL FROM ODBCAccess
> DENY SELECT ON dbo.syscolumns TO ODBCAccess
> DENY SELECT ON dbo.syscomments TO ODBCAccess
> DENY SELECT ON dbo.sysdepends TO ODBCAccess
> DENY SELECT ON dbo.sysfilegroups TO ODBCAccess
> DENY SELECT ON dbo.sysfiles TO ODBCAccess
> DENY SELECT ON dbo.sysfiles1 TO ODBCAccess
> DENY SELECT ON dbo.sysforeignkeys TO ODBCAccess
> DENY SELECT ON dbo.sysfulltextcatalogs TO ODBCAccess
> DENY SELECT ON dbo.sysfulltextnotify TO ODBCAccess
> DENY SELECT ON dbo.sysindexes TO ODBCAccess
> DENY SELECT ON dbo.sysindexkeys TO ODBCAccess
> DENY SELECT ON dbo.sysmembers TO ODBCAccess
> DENY SELECT ON dbo.sysobjects TO ODBCAccess
> DENY SELECT ON dbo.syspermissions TO ODBCAccess
> DENY SELECT ON dbo.sysproperties TO ODBCAccess
> DENY SELECT ON dbo.sysprotects TO ODBCAccess
> DENY SELECT ON dbo.sysreferences TO ODBCAccess
> DENY SELECT ON dbo.systypes TO ODBCAccess
> DENY SELECT ON dbo.sysusers TO ODBCAccess
> --allow selecting
> GRANT SELECT (idx, col1, col2) ON tblAllData TO ODBCAccess
> GRANT SELECT ON qryAllData TO ODBCAccess
> </pre>
>
>
>
> TIA,
> M

Dan Guzman wrote:

> You can specify WITH VIEW_METADATA so that only meta-data exposed by the
> view is visible:
>
> CREATE VIEW MyView
> WITH VIEW_METADATA AS
> SELECT MyPublicData FROM MyTable

"Morten Mikkelsen" <mbmnewsreader_@_mikkelsens.net> wrote in message
news:437fa8ec$0$47014$edfadb0f@dread15.news.tele.dk...
> Dan Guzman wrote:
>
>> You can specify WITH VIEW_METADATA so that only meta-data exposed by the
>> view is visible:
> >
>> CREATE VIEW MyView
>> WITH VIEW_METADATA AS
>> SELECT MyPublicData FROM MyTable
>
>
> This is a bit better.
> However, now, when using excel to extract the data, the query designer
> shows both MyView and MyTable in the dropdown for selecting the source.
> If I select the table as source, the secret columns still show up.
> How do I remove the table from the list of selectable choices while still
> allowing them to select the data from it through the view?
>
> TIA,
> /M
>

> The user has to have select permissions on the table for the view to work,
> right?

"Morten Mikkelsen" <mbmnewsreader_@_mikkelsens.net> wrote in message
news:43805880$0$47068$edfadb0f@dread15.news.tele.dk...
> Dan Guzman wrote:
>> Does the user have permissions on the table? In that case, the table
>> will be visible in the list.
>
> The user has to have select permissions on the table for the view to work,
> right?
>
>
> /M

Dan Guzman wrote:
> Does the user have permissions on the table? In that case, the table will
> be visible in the list.