Security question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Security question

Security question

Rate Topic

Display Mode

Topic Options

Author

Message

sunny.tjk

Posted Monday, November 05, 2012 8:36 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:26 PM
Points: 263, Visits: 866

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

Post #1381127

anthony.green

Posted Monday, November 05, 2012 8:41 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1381132

sunny.tjk

Posted Monday, November 05, 2012 9:03 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:26 PM
Points: 263, Visits: 866

anthony.green (11/5/2012)

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

Post #1381140

Ratheesh.K.Nair

Posted Monday, November 05, 2012 9:21 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:21 AM
Points: 2,415, Visits: 3,370

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.

Post #1381153

sunny.tjk

Posted Monday, November 05, 2012 9:48 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:26 PM
Points: 263, Visits: 866

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?

Post #1381175

anthony.green

Posted Tuesday, November 06, 2012 1:49 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1381440

Posted Tuesday, November 06, 2012 4:00 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:42 AM
Points: 299, Visits: 478

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.

Post #1381478

« Prev Topic | Next Topic »

Permissions