Security question

  • Is it possible to restrict from adding new members to db_datareader role?

  • If you impliment the right security at the login level to prevent people adding people into the role then yes, but remember if a user has sysadmin rights they can do what they want even if you put an explict deny on the operation.

  • anthony.green (11/5/2012)


    If you impliment the right security at the login level to prevent people adding people into the role then yes, but remember if a user has sysadmin rights they can do what they want even if you put an explict deny on the operation.

    That doesn't sound like an option since we'd like to restrict everyone even sysadmins.

  • Then you start from top.First revoke sysadmin privileges and give less privilege to them better restrict them to database roles and then you can implement.

  • Is it possible to create a sql job which runs every 15 mins to check if any user is added to the db_datareader database role?

  • Yes you can do that sort of thing if you want, just need to query the correct tables to get the information out and check it against a previosu run to capture any differences, alternativly setup a trace which does what you need and you can just review the trc file.

  • DDL Triggers can help you.

    ---------------------------------------------------------------------

    Create Trigger Deny_db_datareader

    on Database

    for ADD_ROLE_MEMBER

    as

    begin

    SELECT 1 where EVENTDATA().value

    ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') Like '%sp_addrolemember%db_datareader%'

    If @@ROWCOUNT <> 0

    Begin

    Print 'Add Rolemember being called in this database.'

    ROLLBACK

    End

    Print 'No Issues.'

    end

    ---------------------------------------------------------------------------------------------

    Better try this in non-prod environment.

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

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