Deny delete and drop permission to the sql user

  • Hi,

    how to deny delete and drop permission to the sql user for a particular database in sql server 2005

    I tried with roles and other permission

    i am not able to deny both delete and drop permission.

    Only delete and drop permission has to be denied rest update,insert and alter permissions should be given

    Pls help me

  • use [Your_Database]

    GO

    GRANT INSERT TO [Your_User]

    GO

    use [Your_Database]

    GO

    GRANT SELECT TO [Your_User]

    GO

    use [Your_Database]

    GO

    GRANT UPDATE TO [Your_User]

    GO

    use [Your_Database]

    GO

    DENY DELETE TO [Your_User]

    GO

    Your_User --- is user that you want grant select insert & update but not to drop or delete!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Hi dugi,

    Thanks for the reply

    now u have restricted for delete same way can i restrict for drop permission also right

    use Database

    go

    deny drop to user

    Thanks,

    Anjan

  • hmmm...did you try to drop any object after the script above ...ok make a test go create TEST db then create 3-4 table then apply again the script that I posted above and I'm sure that you can not drop = delete any object!

    Finally I can see you that you didn't try to delete ...ok don't do it in production server do some test before i told you to create one simple db with 3-4 tables!

    :w00t::w00t::w00t::w00t:

    Cheers!

    Dugi

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • ...I forgot for the type of user... the first post it was for SQL Server user so if you have domain users you should write like this for the user "Domain\Your_User"

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • hi dugi

    one problem delete permission is restricted but the testuser can drop the table

    any idea how to fix this issue?

  • Hi steve

    do u have any idea

  • anjan.ashok (10/7/2008)


    hi dugi

    one problem delete permission is restricted but the testuser can drop the table

    any idea how to fix this issue?

    Maybe you didn't run the script in correct and can you tell me you user, member of what role is!?

    Try that your user to be the member of "db_datawriter" role!

    Here is the commands:

    DELETE PAYROLL

    WHERE No = 2;

    MSG:

    Msg 229, Level 14, State 5, Line 1

    The DELETE permission was denied on the object 'Payroll', database 'TEST', schema 'dbo'.

    And

    DROP TABLE PAYROLL;

    GO

    MSG:

    Msg 3701, Level 14, State 20, Line 4

    Cannot drop the table 'Payroll', because it does not exist or you do not have permission.

    For me works fine and everything is correct!

    :w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • hi Dugi,

    Thanks buddy

    i added to datawriter role and now drop permission is denied.

    One more question?

    When i tried to create table ,i am getting following error

    Msg 2760, Level 16, State 1, Line 1

    The specified schema name "dbo" either does not exist or you do not have permission to use it.

    when i tried to alter table i am getting following error

    Msg 1088, Level 16, State 13, Line 1

    Cannot find the object "test1" because it does not exist or you do not have permissions.

    To fix can i assign permissions at the database level or can i do through grant permission i guess its not possible through grant query

    pls help me on this.

    Thanks,

    Anjan:)

  • anjan.ashok (10/8/2008)


    hi Dugi,

    Thanks buddy

    i added to datawriter role and now drop permission is denied.

    One more question?

    When i tried to create table ,i am getting following error

    Msg 2760, Level 16, State 1, Line 1

    The specified schema name "dbo" either does not exist or you do not have permission to use it.

    when i tried to alter table i am getting following error

    Msg 1088, Level 16, State 13, Line 1

    Cannot find the object "test1" because it does not exist or you do not have permissions.

    To fix can i assign permissions at the database level or can i do through grant permission i guess its not possible through grant query

    pls help me on this.

    Thanks,

    Anjan:)

    Ok you have also these options:

    use [TEST]

    GO

    GRANT ALTER TO [Your_User]

    GO

    use [TEST]

    GO

    GRANT CREATE TABLE TO [Your_User]

    GO

    use [TEST]

    GO

    GRANT CREATE VIEW TO [Your_User]

    GO

    The last one is also to create the view!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Hi dugi,

    If i grant create table and alter permission, then the user can drop the table i have crosschecked, but if deny createtable and alter table then the user can't drop table.

    So how fix this.:hehe:

    Thanks,

    Anjan

  • anjan.ashok (10/9/2008)


    Hi dugi,

    If i grant create table and alter permission, then the user can drop the table i have crosschecked, but if deny createtable and alter table then the user can't drop table.

    So how fix this.:hehe:

    Thanks,

    Anjan

    You have confusion attack, try from begining create the user then add datawriter role then run this script:

    use [Your_Database]

    GO

    GRANT INSERT TO [Your_User]

    GO

    use [Your_Database]

    GO

    GRANT SELECT TO [Your_User]

    GO

    use [Your_Database]

    GO

    GRANT UPDATE TO [Your_User]

    GO

    use [Your_Database]

    GO

    DENY DELETE TO [Your_User]

    GO

    use [Your_Database]

    GO

    GRANT ALTER TO [Your_User]

    GO

    use [Your_Database]

    GO

    GRANT CREATE TABLE TO [Your_User]

    GO

    use [Your_Database]

    GO

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Hi, i have similar problem sometime ago. I have a user that need to be dbo for some reason but i neet to limited to "no drop".

    i found the solution using DENY ALTER ON SCHEMA::[dbo] to [someuser]

  • how to give permission to create table and deny drop;

    DENY ALTER ON SCHEMA::DBO TO username -- if we aplly this then we cannot create table.

    i need to give permission to create table and deny drop

Viewing 14 posts - 1 through 13 (of 13 total)

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