Solved

Allow or deny access to SQL server with Active Directory groups

Posted on 2013-06-07
3
299 Views
Last Modified: 2013-12-05
Hello;

We're looking for a simple way to create a group in Active Directory (called, for example, SQLUsers), then ONLY allow any access to a particular SQL server/instance to that group.

This does not need to be granular, or per-database.  Users not in this group should not have any access whatsoever to the SQL server, the instance or any database within it.  So I'm not interested in fine-tuning LEVELS of access (that we'll leave to SQL itself and/or the application that uses SQL), this is just for simply allowing (or disallowing) basic connectivity to SQL Server.

Ideas?  Thanks all.
0
Comment
Question by:Uptime Legal Systems
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39229478
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39229718
If this is a preexisting instance, I would also peruse what login principals are there, as you may want to explicitly deny login for those principals/remove them (however, be careful to make sure that you don't break any external tasks/SQL Agent Jobs that may run/access the instance in that context).
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 39230798
The below code should help...

use master;
deny connect sql to [ YOURDOMAIN\BLACKLISTGROUP] cascade;
go

Open in new window

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question