|
|
Posted by Dan Guzman on 09/27/53 11:32
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
--
Hope this helps.
Dan Guzman
SQL Server MVP
"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
Navigation:
[Reply to this message]
|