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

Best way to completely keep an entire Active Directory group of people out of a SQL Server Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 10:52 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
I'd like to ban an entire group from accessing specific SQL Servers, does anyone know of a solid way to approach this?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1448142
Posted Tuesday, April 30, 2013 11:08 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
If they're in a Windows security group, you can add the group as a login and deny permission to connect to the SQL Server.

However, this is usually seen as a method of last resort. Someone could always be added to the group that you didn't intend to block.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1448152
Posted Tuesday, May 28, 2013 11:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 6,752, Visits: 14,397
I use a logon trigger to acheive this, blocking an AD group accessing via certain applications, but the trigger could quite easily just block the group.

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1457461
Posted Wednesday, May 29, 2013 5:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 22, 2014 5:53 AM
Points: 51, Visits: 296
If it is general blocking a set of users then a logon trigger will do the trick.

But if you want to block Server Admins & Developers with Server Admin rights then it gets more tricky as they can always start sql with single user mode and minimal configuration which will prevent the login triggers from running.

Post #1457663
Posted Tuesday, June 25, 2013 7:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:59 PM
Points: 333, Visits: 555
K. Brian Kelley (4/30/2013)
If they're in a Windows security group, you can add the group as a login and deny permission to connect to the SQL Server.

However, this is usually seen as a method of last resort. Someone could always be added to the group that you didn't intend to block.


This is the way I would do it.

Alternatively, and to prevent the chance that you block anyone inavertently from accessnig the server, you could create a new AD Security Group and place the members you explicitly don't want on the server in that group. All you need to do then is distribute the group to the required servers and deny connect to that group.
Post #1467134
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse