I have a developer that just accidentally ran an UPDATE and ALTER TABLE script he was working on in a production database instead of the development environment.

How can I track down what security group allowed such a behavior? I have the AD login used, as well as the target database.

I could go the long way around, looking into every single AD group and sub-group this user is a member of, and check for each one if it grants some kind of security in my SQL server. But it is going to take hours. And that would only cover one user out of a dozen.

I would like to track permissions (other than READ or VIEW DEFINITION), and their AD members. Either selecting an AD user and look through what they have access to, or the other way around, selecting a database and getting everyone that has access to to it (and how).

I can handle a PowerShell solution too if someone has some code to share (PS newbie).

  • Maybe won't get a quick answer on this as I don't know if there is a tool or script out there that will do this. If it were me, I would look at table permssions--hopefully not much of anything assigned at that level, then schema, then database permissions. Hopefully you won't have very many groups that have the ability to alter tables in production--if you do, then you likely need a complete security review/redesign. Hopefully you've got a short list of groups that have these permissions on database, schema, and table level, so now you just have to see which of those groups the user is in. – Tony Hinkle May 14 '20 at 15:17
  • Comment above assumes suspect groups have not been given db_owner or sysadmin or some such, which we would assume you've already checked. – Tony Hinkle May 14 '20 at 15:18
  • Exact same question--does this answer it, at least to the point of getting the groups? dba.stackexchange.com/questions/17961/… add where grantee.name <> 'public' to eliminate all of the public permissions. – Tony Hinkle May 14 '20 at 15:40
  • If you looked at the answer I posted, I have updated it to get all of the groups the user is a member of either directly or indirectly. – Tony Hinkle May 14 '20 at 19:41
  • I didn't see that post in my searches. Good catch Tony. Althou even there, the "how it got this permission" is still not answered. – Philippe May 15 '20 at 15:12

Following is a partial solution that will return permissions assigned to objects in a given database to groups that a user is a member of either directly or indirectly. The script loops through the groups the user is a member of, and gets all of the groups those groups are members of recursively to build a list that is used for a WHERE IN predicate.

The complexity of a full solution lies in the multiple ways permissions can be gained (through direct assignment to any number of groups, membership in roles, etc.) For example, if you add an AD group to a custom role, then you have to check if that role has permissions. Also this only shows assignments, not effective permissions, so if there are DENYs you have to take those into account to determine effective permissions.

You'll need the Active Directory PowerShell module installed for this, as well as the SQL Server client module.

$Username = "USERNAME"
$DomainName = "CONTOSO\"
$SqlServer = "SqlServerName"
$Database = "DatabaseName"

Function Get-ADGroupMemberOfRecursive{
        $Results += "'$DomainName" + $Group + "', "
            ForEach($Object in (get-adgroup $Group -properties memberof).memberof | get-adgroup | select name){
                Get-ADGroupMemberOfRecursive $Object.name
        $Results | Select -Unique | out-string

ForEach($Object in (get-aduser $Username -properties memberof).memberof | get-adgroup){
    [string]$GroupList += Get-ADGroupMemberOfRecursive $Object.name

$GroupList = $GroupList.substring(0, $GroupList.length - 4)

$Query = "SELECT `
        dp.class_desc `
       ,dp.permission_name `
       ,dp.state_desc `
       ,ObjectName = OBJECT_NAME(major_id) `
       ,GranteeName = grantee.name `
       ,GrantorName = grantor.name `
    FROM `
        sys.database_permissions dp `
        JOIN sys.database_principals grantee `
        ON dp.grantee_principal_id = grantee.principal_id `
        JOIN sys.database_principals grantor `
        ON dp.grantor_principal_id = grantor.principal_id `
    WHERE grantee.name IN (" + $GroupList + ")"

invoke-sqlcmd -serverinstance $SqlServer -Database $Database -Query $Query | ft
  • This is a great start indeed! I ran it as-is just to check the kind of results it gave out, and I got some AD errors that seems to indicate some cleanup in my AD structure is in order ("Cannot find object with identity" on some of the groups the account is part of). – Philippe May 15 '20 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.