Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Security question Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 8:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:50 AM
Points: 299, Visits: 1,129
Is it possible to restrict from adding new members to db_datareader role?
Post #1381127
Posted Monday, November 5, 2012 8:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:10 AM
Points: 5,221, Visits: 5,119
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
Posted Monday, November 5, 2012 9:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:50 AM
Points: 299, Visits: 1,129
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.
Post #1381140
Posted Monday, November 5, 2012 9:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 10:44 AM
Points: 2,634, Visits: 3,985
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
Posted Monday, November 5, 2012 9:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:50 AM
Points: 299, Visits: 1,129
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
Posted Tuesday, November 6, 2012 1:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:10 AM
Points: 5,221, Visits: 5,119
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.



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 #1381440
Posted Tuesday, November 6, 2012 4:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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 »

Add to briefcase

Permissions Expand / Collapse