Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2008
    Posts
    7

    Question Unanswered: How To Disable Dml Commands In Sql Query Analyser

    I have to disable update, delete and insert commands in my sql query analyser window

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You must deny these permissions to the users / groups.
    Example
    Code:
    DENY DELETE ON employees TO george
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ...or roles
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gsbeula
    I have to disable update, delete and insert commands in my sql query analyser window
    i would simply stop issuing those particular statements


    patient: doc, it hurts when i do this

    doctor: don't do it then

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you certain you aren't a DBA Rudy?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i am certain

    a DBA would answer the question with gobbledegook about roles and groups and permissions

    an SQL Consultant would simply say "if you no wants to run update, insert, delete statements in your QA, then don't run update, insert, delete statements in your QA"

    see? simple

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And that is why regular IT boffins stereotypically dislike consultants
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    gee, you would think that a consultant would propose a 9 step plan to impliment proper change and configuration management through the use of this handy peice of development shop management software.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    oh and such an understaking will take the hiring of at least 6 other consultants that the first consultant happens to know personally.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It isn't all that surprising that they know one another. When they all have the same surname, they tend to queue up next to one another quite frequently.

    -PatP

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Pat Phelan
    It isn't all that surprising that they know one another. When they all have the same surname, they tend to queue up next to one another quite frequently.

    -PatP
    That went wayyy over my head
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First, read post #9. Next, read post #10. They go in sequence.

    -PatP

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Penny has dropped
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2008
    Posts
    7

    Deny permission for all users and databases

    i have to deny permission for all users except super user and databases, ie any user except the super user cannot execute DML commands.... but the superuser(sa) have to execute all the commands...





    Quote Originally Posted by georgev
    You must deny these permissions to the users / groups.
    Example
    Code:
    DENY DELETE ON employees TO george

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The sa login becomes dbo (the database owner) by default. You can't deny DML from either of them, the database engine will ignore any attempt to do that.

    Users need to be granted permissions, one at a time. The simple solution from my perspective is not to grant any permissions in the first place. If you don't give the user a permission, then they won't have that permission.

    Another way to get what you want (so that ordinary users can't change data) is to add each user/role to the db_denydatawriter role. This would get the same effect, but with much less work and no chance for errors.

    -PatP
    Last edited by Pat Phelan; 02-24-08 at 03:48. Reason: Fixed typo

Posting Permissions

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