InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 16:15:18 GMT20http://www.sqlservercentral.com/Forums/Topic1381127-1550-1.aspxDDL Triggers can help you.---------------------------------------------------------------------Create Trigger Deny_db_datareader on Databasefor ADD_ROLE_MEMBERasbegin SELECT 1 where EVENTDATA().value ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') Like '%sp_addrolemember%db_datareader%'If @@ROWCOUNT <> 0BeginPrint 'Add Rolemember being called in this database.' ROLLBACKEndPrint 'No Issues.' end---------------------------------------------------------------------------------------------Better try this in non-prod environment.Tue, 06 Nov 2012 04:00:07 GMTSQL Showhttp://www.sqlservercentral.com/Forums/Topic1381127-1550-1.aspxYes 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.Tue, 06 Nov 2012 01:49:16 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1381127-1550-1.aspxIs 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?Mon, 05 Nov 2012 09:48:00 GMTsunny.tjkhttp://www.sqlservercentral.com/Forums/Topic1381127-1550-1.aspxThen 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.Mon, 05 Nov 2012 09:21:50 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1381127-1550-1.aspx[quote][b]anthony.green (11/5/2012)[/b][hr]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.[/quote]That doesn't sound like an option since we'd like to restrict everyone even sysadmins.Mon, 05 Nov 2012 09:03:05 GMTsunny.tjkhttp://www.sqlservercentral.com/Forums/Topic1381127-1550-1.aspxIf 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.Mon, 05 Nov 2012 08:41:41 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1381127-1550-1.aspxIs it possible to restrict from adding new members to db_datareader role?Mon, 05 Nov 2012 08:36:04 GMTsunny.tjk