locked
Deny Delete to a user doesnt work RRS feed

  • Question

  • i gave a sql server user access to insert ,update,alter and execute.but if i dont Grant alter permissions to the user then the user is not able to create a table.if i grant alter permissions the user is able to delete the table.

    How can i allow the user to create the tables but  DENY from Deleting them

    Below are the queries i used.

    USE [Databasename]

    GO
    GRANT INSERT TO sqluser
    GO

    GRANT CREATE TABLE TO sqluser
    GO

    GRANT SELECT TO sqluser
    GO

    GRANT UPDATE TO sqluser
    GO

    GRANT  Execute TO sqluser
    GO

    GRANT ALTER TO sqluser --if i deny alter sqluser is not able to create tables
    GO

    DENY DELETE  TO sqluser --Doesnt work..still allows sqluser to delete the table
    GO

    Revoke DELETE  TO sqluser --doesnt work..user is still able to delete the table

    GO

    Thank you


    lucky



    • Edited by LuckyDba Tuesday, April 16, 2013 4:22 PM
    Tuesday, April 16, 2013 4:20 PM

Answers

  • Both CREATE TABLE and DROP TABLE require ALTER permissions on the schema (or whole database).  The CREATE TABLE permission is a database-level permission that is required to access the database storage.

    So there is no permission that will allow a user to CREATE but not DROP a table.  You could use a DDL trigger to prevent a DROP TABLE based on some other criteria.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by LuckyDba Tuesday, April 16, 2013 4:40 PM
    Tuesday, April 16, 2013 4:37 PM
  • If you create a trigger on table where users should not delete..

    You can use this as a option instead of Deny Delete, Because some times that particular user need to delete some other data in tables when he/she is a developer.

    CREATE TRIGGER <TriggerName> ON <TableName>
    INSTEAD OF DELETE
    AS
    ROLLBACK
    RAISERROR('ERROR, DELETE’s not permitted in <YourTableName>!!!',16,1)
    RETURN

    GO

    • Marked as answer by LuckyDba Monday, April 7, 2014 6:55 PM
    Wednesday, April 2, 2014 11:21 AM

All replies

  • Both CREATE TABLE and DROP TABLE require ALTER permissions on the schema (or whole database).  The CREATE TABLE permission is a database-level permission that is required to access the database storage.

    So there is no permission that will allow a user to CREATE but not DROP a table.  You could use a DDL trigger to prevent a DROP TABLE based on some other criteria.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by LuckyDba Tuesday, April 16, 2013 4:40 PM
    Tuesday, April 16, 2013 4:37 PM
  • Thank you so much for the quick response.

    lucky

    Tuesday, April 16, 2013 4:40 PM
  • If you create a trigger on table where users should not delete..

    You can use this as a option instead of Deny Delete, Because some times that particular user need to delete some other data in tables when he/she is a developer.

    CREATE TRIGGER <TriggerName> ON <TableName>
    INSTEAD OF DELETE
    AS
    ROLLBACK
    RAISERROR('ERROR, DELETE’s not permitted in <YourTableName>!!!',16,1)
    RETURN

    GO

    • Marked as answer by LuckyDba Monday, April 7, 2014 6:55 PM
    Wednesday, April 2, 2014 11:21 AM