Post reply

Prevent Record Deletion

  • December 17, 2019 at 8:24 pm

    #3708603

    SQL Server 2016. Used as a front-end to an Access database app for an employee database.

    In the Employee table, there is never a good reason to delete a row. How can I set rights on the server, such that the user cannot delete any record from the employee table?

    I've tried:  DENY DELETE ON OBJECT::dbo.EMPLOYEE TO sysadm;

    But can still delete records.

  • December 17, 2019 at 8:33 pm

    #3708604

    How are the users of the access database authenticated in SQL Server?

  • December 17, 2019 at 8:38 pm

    #3708606

    Using an ODBC connection.

  • December 17, 2019 at 9:07 pm

    #3708618

     

    Are the users authenticated using integrated security or seperate sQL Server accounts or does the Access application connect using one SQL Server login or application group?

  • December 18, 2019 at 6:21 pm

    #3708870

    I've created 1 SQL Server account. The ODBC connection string is coded in the app. When the app opens, it establishes the ODBC connection. So all users are using the same SQL account.

  • December 18, 2019 at 7:23 pm

    #3708884

    Is this a direct DELETE statement - or are they calling a stored procedure to perform the delete?  If calling a procedure - and they have execute permissions on the procedure then they can run that code and it will execute.

    If this a delete option from a form - that is directly tied to the table - and the user account has not been granted DELETE or has been denied DELETE on the object then they should not be able to delete from the table.

    My guess is - either a stored procedure or the DENY is not applied to the correct login/user or the login has elevated rights (sysadmin?).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • December 18, 2019 at 9:28 pm

    #3708917

    Hmmm... maybe an "Instead-of" trigger to reject DELETEs?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 18, 2019 at 10:18 pm

    This was removed by the editor as SPAM

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

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