Sql-server – Determine the source of user permissions in a database

powershellSecuritysql serversql-server-2016t-sql

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).

Best Answer

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{
Param([String]$Group)
    Begin{
        $Results += "'$DomainName" + $Group + "', "
    }
    Process{
            ForEach($Object in (get-adgroup $Group -properties memberof).memberof | get-adgroup | select name){
                Get-ADGroupMemberOfRecursive $Object.name
            }
    }
    End{
        $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
Related Question