Results 1 to 4 of 4

Thread: SP permission

  1. #1
    Join Date
    May 2009
    Posts
    10

    Unanswered: SP permission

    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 dont.


    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 automatically?
    Can I implement something like that?
    Ive tried DDL trigger but it fires before SP gets created so its not helpful.I am using sql 2005. Any ideas? Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Find yourself a nice 10 Lb sledge hammer. Any time a developer forgets to add the permissions, take the hammer, and apply it to the smallest toe left on the right foot of that developer. Problem should disappear organically after several applications.

    In SQL 2005, you may be able to set up some template scripts to help the developers along, but it really does boil down to whether the developers remember or not.

  3. #3
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    Could you take the opposite approach and GRANT execute on everything? GRANT EXECUTE TO [my_user]

    If there are any procs that they shouldn't have access to you can individually DENY those rights.

    Obviously this isn't right for every solution, but if it's a small home grown app, that uses a single application account, it may be easiest.
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  4. #4
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    I don't agree with the "grant all" approach. Setting yourself up for too many potential problems down the road by having to "remember" to DENY rights. I, personally, would prefer a user cannot execute something than have them be able to execute something they shouldn't have access to. See mcrowley's response for how to ensure the "grant" happens with regularity!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •