Hide Base Tables: Select + Update using Views [Access/SQL2000]

• john.bosco

  SSC Enthusiast

  Points: 144

  More actions

  December 21, 2009 at 1:34 pm

  #103869

  For Users[+ db Roles]:

  Can I disallow Select on a table, yet give the users Select + Update on a View based on the table?

  Is the task much simpler if we switch from SQL2000 to SQL2005?

  ANY help is always appreciated.

  jb

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  December 22, 2009 at 1:17 am

  #1095274

  It is possibile. It isn't simpler with SQL2005, mainly because it couldn't be any simpler.

  Here's an example with SQL2005:

  USE testDB

  CREATE TABLE testtable (

  test INT

  )

  INSERT INTO testtable VALUES(1)

  INSERT INTO testtable VALUES(2)

  INSERT INTO testtable VALUES(3)

  GO

  CREATE VIEW testview

  AS

  SELECT * FROM testtable

  DENY UPDATE ON testtable TO testuser

  EXECUTE('UPDATE testtable SET test = test + 1') AS USER = 'testuser'

  --Msg 229, Level 14, State 5, Line 1

  --The UPDATE permission was denied on the object 'testtable', database 'TestDB', schema 'dbo'.

  EXECUTE('UPDATE testview SET test = test + 1') AS USER = 'testuser'

  --(3 row(s) affected)

  DROP VIEW testview

  DROP TABLE testtable

  Regards

  Gianluca

  -- Gianluca Sartori