Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

Allow or deny access to SQL server with Active Directory groups

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
Uptime Legal Systems
Asked:
Uptime Legal Systems
1 Solution
 
Shaun KlineLead Software EngineerCommented:
0
 
didnthaveanameCommented:
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
 
Racim BOUDJAKDJICommented:
The below code should help...

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

Open in new window

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now