locked
Can I deny DELETE on a specific table for a given database role or user in SSMS? RRS feed

  • Question

  • Hello,

    I have been trying to find an answer on the internet.  A lot of information, still confused! 

    Can I deny DELETE on a specific table for a given database role or user in SSMS?   I want the role or users to be able to ADD and UPDATE, but not DELETE.   If so, how do I do so?

    Thanks so much for you help!

    smsemail

    Monday, October 14, 2013 5:42 PM

Answers

  • Well. The code up there is all you need. Why make it more comlicated?

    But if you really can't copy & paste & edit it to your needs (good for documentation by the way):

    Yes, of course it's also "clickable"

    Go to the "Security"-node under the database, search for the User, open his Properties, "Securables" and follow the click-cascade to get the Schema/Table you need. (Believe me, it's way more complicated than running these 3 lines code.)


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Proposed as answer by Saeid Hasani Monday, October 14, 2013 7:08 PM
    • Marked as answer by smsemail Monday, October 14, 2013 7:12 PM
    Monday, October 14, 2013 6:59 PM
  • And these are the screenshots:


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    • Marked as answer by smsemail Monday, October 14, 2013 7:23 PM
    Monday, October 14, 2013 7:07 PM

All replies

  • Of course you can

    all you have to do is to

    GRANT INSERT, UPDATE, SELECT
    ON Schema.TableName   -- if you really can't use the whole Schema
    To Role/UserName

    But if he already has DELETE permission from some other role, you would indeed use

    DENY DELETE

    ON Schema.TableName   

    To Role



    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com


    Monday, October 14, 2013 6:05 PM
  • Thanks so much for your help Andreas,

    Can I do this using Object Explorer in SSMS.  Do I have to do this in T-Sql?

    smsemail

    Monday, October 14, 2013 6:42 PM
  • Well. The code up there is all you need. Why make it more comlicated?

    But if you really can't copy & paste & edit it to your needs (good for documentation by the way):

    Yes, of course it's also "clickable"

    Go to the "Security"-node under the database, search for the User, open his Properties, "Securables" and follow the click-cascade to get the Schema/Table you need. (Believe me, it's way more complicated than running these 3 lines code.)


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Proposed as answer by Saeid Hasani Monday, October 14, 2013 7:08 PM
    • Marked as answer by smsemail Monday, October 14, 2013 7:12 PM
    Monday, October 14, 2013 6:59 PM
  • And these are the screenshots:


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    • Marked as answer by smsemail Monday, October 14, 2013 7:23 PM
    Monday, October 14, 2013 7:07 PM
  • Thanks so much for your help.  Very much appreciated.

    smsemail

    Monday, October 14, 2013 7:12 PM
  • Thanks Hasani so much for the visual.  Very much appreciated!
    Monday, October 14, 2013 7:23 PM