none
SQL Server : How to deny access?

    Question

  • Hi,

    I want to ask how to deny users to delete tables or more specifically deny DML statements but allow DDL statements in database.

    It's urgent.


    Satya Prakash Jugran
    Wednesday, October 20, 2010 11:01 AM

Answers

  • Satya Prakash Jugran

    The  you need to have DDL trigger to prevent from deletion

    --Preventing changes to database objects
    CREATE TRIGGER trgNoMonkeying ON DATABASE
        FOR DROP_PROCEDURE

     


    AS
        DECLARE @Message VARCHAR(255)
        SELECT  @message = 'You are forbiddent to delete the '''
                + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
                                    'nvarchar(100)') + ''' PROCEDURE'
        RAISERROR ( @Message, 16, 1 )
        ROLLBACK ;
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, October 20, 2010 1:43 PM

All replies

  • DENY  UPDATE, DELETE, INSERT on SCHEMA::dbo TO username
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, October 20, 2010 11:40 AM
  • HI,

    Actually, someone has deleted some stored procedures and functions in server database. I want to restrict the user as:

    1) They can insert update and delete the data.

    2) They can create the procedures and functions.

    3) They should not be able to delete the data, procedures and functions once created.

    I need the resolution to the third point.

    Pls Help!!!


    Satya Prakash Jugran
    Wednesday, October 20, 2010 1:07 PM
  • Please add the user  db_denydatawriter fixed db role.

    Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database

     

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Wednesday, October 20, 2010 1:09 PM
  • Hi Siva,

    I want users to add or modify something but not to delete.


    Satya Prakash Jugran
    Wednesday, October 20, 2010 1:17 PM
  • Satya Prakash Jugran

    The  you need to have DDL trigger to prevent from deletion

    --Preventing changes to database objects
    CREATE TRIGGER trgNoMonkeying ON DATABASE
        FOR DROP_PROCEDURE

     


    AS
        DECLARE @Message VARCHAR(255)
        SELECT  @message = 'You are forbiddent to delete the '''
                + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
                                    'nvarchar(100)') + ''' PROCEDURE'
        RAISERROR ( @Message, 16, 1 )
        ROLLBACK ;
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, October 20, 2010 1:43 PM
  • Satya Prakash Jugran

    The  you need to have DDL trigger to prevent from deletion

    --Preventing changes to database objects
    CREATE TRIGGER trgNoMonkeying ON DATABASE
        FOR DROP_PROCEDURE

     


    AS
        DECLARE @Message VARCHAR(255)
        SELECT  @message = 'You are forbiddent to delete the '''
                + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
                                    'nvarchar(100)') + ''' PROCEDURE'
        RAISERROR ( @Message, 16, 1 )
        ROLLBACK ;
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, October 20, 2010 1:43 PM