GRANT I/U/D/S on Newly Created Tables and SPs

• Express12

  SSCoach

  Points: 18514

  More actions

  March 14, 2006 at 8:31 am

  #116434

  Basic request here:   I need T-SQL to GRANT INSERT, UPDATE, DELETE, SELECT on all user TABLES and EXECUTE on all user SPs created after 3/1/2006.

  thx in advance

  BT

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 14, 2006 at 9:02 am

  #626563

  declare @group varchar(128)

  set @group = 'mygroup'

  select 'Grant '+ Case when xtype = 'U' then 'INSERT, UPDATE, DELETE'

                   else 'EXECUTE' end + ' ON ' + name + ' TO ' + @group

  from sysobjects

  where

   xtype in( 'U','P')

      and crdate> '20050101'

      and ObjectProperty(id,'IsMSShipped') = 0

   

  
  * Noel