Performance impact: Linked server security configuration and how it can hurt you

Linchi Shea — Tue, 21 Jul 2009 17:16:00 GMT

If you change the security configuration of a linked server from using login UserA to login UserB, would you expect that to significantly impact the performance of a query that uses the linked server, assuming that both users have permission to select...(read more)

How does that AD user account get access to the database?

Linchi Shea — Thu, 26 Mar 2009 05:19:00 GMT

Granting Active Directory (AD) groups access to a database can quickly become complex when the AD groups may nest many levels. Consider the following scenario:

You are requested to give Joe the same access to a database as John has. You look up John in the database, and find no trace of John. You then find a bunch of AD groups have been granted access to the database. Obviously, John is getting his access to the database through one or more of these AD groups. But exactly how does John get his access via the AD groups? In other words, what are the AD groups through which John gets his database access?

How would you approach this request? You can fire up the AD Users & Computers management console, look up the user account for John, find all the AD groups John is a member of, and then for each such group, recursively find all the groups this group is a member of.

Unfortunately, that is a labor intensive process. There is a better way.

First, note that you can use two AD utilities, DSquery and DSget, to recursively retrieve all the groups that John is a member of--either directly or indirectly--in a single line of command as follows:

cmd>dsquery user -samid John | dsget user -memberof -expand | findstr OU=Groups

In addition, you can run the following SQL query to find what AD groups have been granted access to the database:

select s1.name

from syslogins s1

join <your database>..sysusers s2 on s1.sid = s2.sid

and s2.hasdbaccess = 1

where s1.isntgroup = 1 and s1.hasaccess = 1 and s1.sysadmin = 0

order by s1.name;

Now, if you compare these two results of the AD groups, and the ones that are common in both results are the AD groups through which John gains access to the database.

So far, all this work is still manual and labor intensive. But it’s not difficult to automate all the steps in a script. The following is a throw-away Perl script I wrote to do precisely that:

use strict;

my ($user_account, $server, $database) = (shift, shift, shift);

my $domain = 'NYD'; # domain name hardcoded. Change it, if necessary

my @groups = `dsquery user -samid $user_account | dsget user -memberof -expand | findstr OU=Groups`;

my @ADGroups;

# Extract the AD group names and prefix them with the domain name

foreach my $group (@groups)

{

if ($group =~ /\"CN=([^,]+)\,/)

{

@ADGroups = (@ADGroups, uc("$domain\\$1"));

}

my $sql = q/set nocount on;

select s1.name

from syslogins s1 join $database..sysusers s2

on s1.sid = s2.sid and s2.hasdbaccess = 1

where s1.isntgroup = 1 and s1.hasaccess = 1 and s1.sysadmin = 0

order by s1.name;

my $rc = `osql -S$server -E -n -h-1 -w200 -Q\"$sql\"`;

$rc =~ s/^\s*//;

$rc =~ s/\s*$//;

my @login_nt_groups = split /\s*\n\s*/, $rc;

my @LoginGroups;

foreach my $nt_group (sort {uc($a) cmp uc($b)} @login_nt_groups)

{

@LoginGroups = (@LoginGroups, uc($nt_group));

}

foreach my $group (getSetCommon([@ADGroups], [@LoginGroups]))

{

print "** $group\n";

}

sub getSetCommon {

my($setRef1, $setRef2) = @_;

my %temp;

my %common;

grep($temp{$_}++, @$setRef2);

map {$temp{$_} and $common{$_}++} @$setRef1;

keys %common;

}

The script takes three command-line parameters: the name of the AD user account (e.g. John in our case), the SQL instance name, and the database name. It retrieves all the AD groups that the user account is in (directly or recursively), retrieves the AD groups that have been granted access to the database, and the find the AD groups that are in both.

This is a quick and dirty approach that seems to work well so far. If you have a better approach, please share with us. Powershell solutions are welcome as well.

Linchi Shea : Security

Performance impact: Linked server security configuration and how it can hurt you

How does that AD user account get access to the database?