November 5, 2012 at 8:36 am
Is it possible to restrict from adding new members to db_datareader role?
November 5, 2012 at 8:41 am
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.
November 5, 2012 at 9:03 am
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.
November 5, 2012 at 9:21 am
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.
November 5, 2012 at 9:48 am
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?
November 6, 2012 at 1:49 am
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.
November 6, 2012 at 4:00 am
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