Solved

Allow or deny access to SQL server with Active Directory groups

Posted on 2013-06-07
3
292 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
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problems "swapping" SQL Server DBs 2 24
SQL Log size 3 20
SQL USE DATABASE VARIABLE 5 31
What is this datetime? 1 20
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

820 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