Results 1 to 13 of 13
  1. #1
    Join Date
    May 2009
    Posts
    13

    Unanswered: Remove alter permissions on Trigger

    Hi,

    i want to remove alter permission on the trigger and i want to give just select
    permission to some users. i am using SQL Server 2005 When i right click on trigger, i am not able to see "properties" option for triggers.

    Is Security not possible on triggers.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote:
    To alter a DML trigger requires ALTER permission on the table or view on which the trigger is defined.

    See here:
    ALTER TRIGGER (Transact-SQL)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you be more specific please. Do you want to prevent them changing the trigger code, but you want them to be able to view the trigger code?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2009
    Posts
    13
    i want to prevent the users to change the trigger code and want to prevent the users to delete the trigger.

  5. #5
    Join Date
    Mar 2009
    Posts
    349
    oh my, your users have that kind of access?

    you could create the trigger specifying WITH ENCRYPTION and store the unencrypted code in a source code solution like SourceGears Vault or or Sourcesafe.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can't prevent that directly, however you can prevent users altering the table the trigger belongs to.

    Code:
    DENY ALTER, DELETE ON dbo.myTable TO userName
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2009
    Posts
    349
    aheemmm

    too short

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasy
    aheemmm

    too short
    Yes?

    too short too
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2009
    Posts
    349
    you sir are violating the number rule of being a software samurai. there is always a solution.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by venubabu
    i want to prevent the users to change the trigger code and want to prevent the users to delete the trigger.
    Quote Originally Posted by Hagakure
    "There is something to be learned from a rainstorm. When meeting with a sudden shower, you try not to get wet and run quickly along the road. By doing such things as passing under the eaves of houses one still gets wet. When you are resolved from the beginning, you will not be perplexed, though you will get the same soaking. This understanding extends to all things."
    too short again
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2009
    Posts
    349
    could we not limit them to the db_reader role or what ever it is called?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's db_datareader, but that is a data permission not a schema one.

    The most granular permission is to restrict DDL changes to the parent table. After that, I guess it is the schema and then the database. I know not of another method.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2009
    Posts
    349
    I do not bother with such issues anymore, but I can certainly query in my test envuronment, but I have no ability to make code changes. I am not sure how they locked things down but my group is in db_datareader and the public roles.

Posting Permissions

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