Elevate permissions within stored procedure

• jcartwright

  Old Hand

  Points: 370

  More actions

  December 17, 2003 at 2:11 pm

  #486836

  Are views updateable in SQL Server 2000? I've never tried to use views beyond a select capability.

  Another option I could employ is to encrypt the data so that it is eseentially useless unless managed through the application.

• K. Brian Kelley

  SSC Guru

  Points: 114642

  More actions

  December 17, 2003 at 2:39 pm

  #486849

  Yes, views are updateable and have their own sets of permissions. So long as the owners are the same between the views and the base tables, ownership chaining occurs, meaning you don't have to assign permissions against the base tables themselves. And encrypting through the app would help.

  K. Brian Kelley, GSEC

  http://www.truthsolutions.com/

  Author: Start to Finish Guide to SQL Server Performance Monitoring

  http://www.netimpress.com/

  K. Brian Kelley
  @kbriankelley

• ThomasH

  SSCertifiable

  Points: 7437

  More actions

  December 17, 2003 at 3:23 pm

  #486859

  SQL Server VIEWs can even limit the data that may be inserted / updated if you use the WITH CHECK OPTION in the view.

  CREATE TABLE A (ID Int, ADate DateTime)

  Then

  CREATE VIEW A_vw as

  SELECT * FROM A Where ADate >= GetDate()

  WITH CHECK OPTION

  INSERT INTO A_vw SELECT 1, GetDate()

  will work, but

  SELECT * FROM A_vw

  will not return any records.

  This is a simplified example, but you can modify it with things like WHERE @@Servername = StoredServerColumn etc.

  
  
  Once you understand the BITs, all the pieces come together