THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

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

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.

 

Published Thursday, March 26, 2009 2:19 AM by Linchi Shea
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Dan said:

You can also use "exec master..xp_logininfo 'domain\username'". It doesn't show as complete a solution as Linchi's as will only return one group membership, but is an quick n dirty check.

March 26, 2009 5:22 AM
 

Jack Corbett said:

What permissions do you need in AD in order to use the AD Utilities to query the AD?

March 26, 2009 8:05 AM
 

Linchi Shea said:

Jack;

All you need is read only. I don't pretend to know all the nuances of the AD permissions, but I beleive every domain user should have that already. I have never had any permission problem using these DS query tools in the domains I've worked, and I was never in any special domain groups. After all, you are expected to be able to use the AD Users & Computers mgmt console. To some extent, that is just a GUI version of these DS tools.

March 26, 2009 11:04 AM
 

jerryhung said:

I get this error for the SQL query on 2005/2008 (clearly not 2000 either)

Invalid object name 'report..sysusers'.

Probably time to use SQLCMD over osql too as well.

This will be handy because we have SQL logins that lose their permission when their domain groups weren't copied over in OS that go under the BUILTIN\Administrators etc..

March 26, 2009 2:41 PM
 

Linchi Shea said:

> Invalid object name 'report..sysusers'.

Oops! I was testing with a hardcoded database name. It should be $database instead. I've made the correction.

March 26, 2009 3:18 PM
 

AaronBertrand said:

Linchi, it's not just in the Perl but also the initial SELECT query.

March 26, 2009 3:39 PM
 

Linchi Shea said:

Thanks Aaron! Updated that as well. Victim of copy/paste.

March 26, 2009 10:14 PM
 

Chad Miller said:

I use a series Powershell functions from the CodePlex project SQL Server Powershell Extensions http://www.codeplex.com/SQLPSX as described in the following articles:

http://www.sqlservercentral.com/articles/powershell/64316/

http://www.sqlservercentral.com/articles/powershell/64350/

The functions recursively enumerate effective members for logins, users, roles and permissions. Here is an example which gets all users including direct and indirect where the effective member is equal to Z002\u00:

get-sqldatabase 'Z002\SqlExpress' pubs | get-sqluser | where {$_.members -contains 'Z002\u00'}

And this is similar example at the instance login level:

get-sqllogin 'Z002\SqlExpress' | where {$_.members -contains 'Z002\u00'}

Once a week I run a Powershell script which is part of CodePlex project that collects effective login, user and other security information and imports the results into a database for all production SQL instances across my enterprise.

April 4, 2009 9:24 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement