Post reply

Users Delete permission issue

  • January 23, 2012 at 4:21 am

    #267011

    I got a SQL Server with 10 databases and main login "LoginGen" which as been mapped to all 10 databases onthe server. On one particular database "Database1" i have user "LoginGen" for login "LoginGen" and i want this

    user to have only read acess on this database and should have any delete permission.

    I tried setting database role to "db_denydatawrite" and even db_datareader", but in all these role i am able to delete records from "Database1" tables.

    So please help me on how to restict a user mapped to a general login to deny delete permission on one database.

  • January 23, 2012 at 4:35 am

    #1437014

    Have you tried DENY DELETE ON TableName TO LoginGen on all tables?

    John

  • January 23, 2012 at 4:37 am

    #1437016

    Is that login a database owner or sysadmin?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 23, 2012 at 4:43 am

    #1437019

    @gilamonster - Yes the login is sysadmin and not a database owner on the database I want

    to deny the delete

  • January 23, 2012 at 4:43 am

    #1437021

    @john-2 Mitchell-245523 - yes, have tried Deny Delete to Logingen as well, no luck.

  • January 23, 2012 at 5:09 am

    #1437025

    You'll need to remove it from sysadmin then as this will override any deny given on the DB, you'll need to correctly map the login and find out the requirements for it's use prior to removing this though if it's currently live as you'll suddenly have no access (unless you've mapped it to another role within that DB)

    Regards,

    Rik

  • January 23, 2012 at 8:49 am

    #1437174

    virgo (1/23/2012)

    @GilaMonster - Yes the login is sysadmin and not a database owner on the database I want to deny the delete

    You cannot deny anything to a sysadmin. They have every single permission on the server.

    Frankly, if they're sysadmin, worrying about their ability to delete rows from a table is odd, it's the least of your concerns. They could drop that table, drop the entire database even, stop SQL, remove other logins, etc, etc

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply