SP Permission

• dallas13

  SSCertifiable

  Points: 7491

  More actions

  June 25, 2009 at 1:26 pm

  #201839

  Usually when developers create any SP in any db, sometimes they include grant execute permission statement at the end of it. But some times they don’t.

  create proc sp_showusers

  as

  select * from users

  where name= 'abc'

  go

  grant execute on sp_showusers to [my_user]

  go

  Is there a way can I create a trigger that fires after Create procedure event happens and it will grant execute permission to that object (sp) automatically?

  Can I implement something like that?

  I’ve tried DDL trigger but it fires before SP gets created so its not helpful. I am using sql 2005. Any ideas? Thanks

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  July 20, 2009 at 3:27 am

  #1026829

  Please, see my reply in your other thread:

  http://www.sqlservercentral.com/Forums/Topic741501-338-1.aspx

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com