Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

RealSQLGuy - Helping You To Become A SQL Hero

My real name is Tracy McKibben. I’ve been working with database products for over 20 years, starting with FoxBase running on Xenix. Over the years, I’ve worked with all flavors of FoxPro, some Clipper and dBase, and starting somewhere around 1995, SQL Server. I’ve even dabbled with Oracle, though I’ve tried to block out all memories of that experience. At present, I’m the Senior SQL Server DBA and the DBA Team Supervisor for Pearson VUE. All opinions expressed on this site are my own and do not reflect the opinions of Pearson VUE.

Automated Permissions Auditing With Powershell and T-SQL: Part 5

rubbergloveshouse

If you’ve followed along to this point, and have assembled the pieces as I’ve given them to you, you’re now looking at a Powershell script that is slowing getting more complex.

By now, you should be starting to appreciate how much power (pun intended) Powershell gives you.  It’s quickly becoming one of my favorite tools, not only for interacting with SQL Server, but for tasks outside as well. It’s great for OS-level operations, and with the proper extensions in place, can even interact with Active Directory.  That’s what we’re going to look at in this segment.

First, let’s recap:

  • in Part 1, I gave you a SQL script to dump SQL Server 2005/2008 permissions
  • in Part 2, I gave you the same script for SQL 2000
  • in Part 3, I showed you how to wrap Powershell around those queries, in order to dump permissions from all databases
  • in Part 4, I showed you to save those permissions to a table

At this point, we have a script capable of dumping all of the database logins and their read/write/dbo/sa permissions for all databases on a given SQL Server instance.  Some of those database logins may be Active Directory domain groups, with multiple users belonging to them.  We can’t see that from inside SQL Server, so if an auditor asks “Who’s in this group?”, we can’t tell them.  With the Free Powershell Commands for Active Directory, a free “CmdLet” (an extension for Powershell) from Quest, we can.

With the CmdLet installed, this simple 2-line script will list the members of an Active Directory group:

<br>Add-PSSnapin Quest.ActiveRoles.ADManagement<br>Get-QADGroupMember RealSQLAD\RealSQLGroup<br>

Doesn’t get any easier than that.  The real magic is yet to come, however, as we integrate that functionality into our audit script.  If you were paying attention during the last segment, you’ll recall that I pointed out the “FullName” column.  If you’ve been paying even closer attention, you’ve noticed that the T-SQL statements from Part 1 and Part 2 return a column named “LoginType”.  Today we’re going to find out why those columns exist.

With all of this data available to us, there’s no reason not to provide some sort of report, makes the management types happy.  Those same management types tend to prefer human names (who is “AD\miljk1″?).  Within our Powershell script, we can look at the “LoginType” column to see that a given database login is a Windows user, and then use the Quest CmdLet to fetch that user’s human name from Active Directory:

if ($row["LoginType"] -eq "WINDOWS_LOGIN")
	{
		$ADUser = Get-QADUser $insertLoginName
		$insertFullName = $ADUser.name
		$insertUserName = $ADUser.ntaccountname
	}

That takes care of individual Windows users, but what about groups?  Almost as easy:

if ($row["LoginType"] -eq "WINDOWS_GROUP")
	{
		$groupMembers = Get-QADGroupMember $insertLoginName -Indirect
		foreach ($member in $groupMembers)
			{
				$insertFullName = $member.name
				$insertUserName = $member.ntaccountname
			}
	}

Put all of this together with our existing script, and here’s what we have:

# Specify server to audit
# Note the non-standard port number of 50000.  This can be omitted if SQL Server is listening on standard port 1433
$auditServerName = "REALSQLGUYSERVER,50000"

# Create connection object to SQL Server instance
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$auditSMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist "$auditServerName"

# Specify server to audit
$targetServerName = "TARGETSERVER"
$targetDBName = "AuditDB"

$targetConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetServerName; Initial Catalog=$targetDBName; Integrated Security=SSPI")
$targetConn.Open()
$targetCmd = $targetConn.CreateCommand()

# Include Quest Active Directory CmdLet
Add-PSSnapin Quest.ActiveRoles.ADManagement

# Create AuditDBLogin table if it doesn't exist
$targetCmd.CommandText = "IF OBJECT_ID('dbo.AuditDBLogin', 'U') IS NULL CREATE TABLE dbo.AuditDBLogin (ServerName VARCHAR(50) NOT NULL, Login VARCHAR(255) NOT NULL, Username VARCHAR(255) NOT NULL, FullName VARCHAR(255) NULL, DatabaseName VARCHAR(255) NOT NULL, SelectAccess BIT NOT NULL DEFAULT (0), InsertAccess BIT NOT NULL DEFAULT (0), UpdateAccess BIT NOT NULL DEFAULT (0), DeleteAccess BIT NOT NULL DEFAULT (0), DBOAccess BIT NOT NULL DEFAULT (0), SysadminAccess BIT NOT NULL DEFAULT (0), CONSTRAINT PK_AuditDBLogin PRIMARY KEY CLUSTERED (ServerName, Login, Username, DatabaseName));"
$null = $targetCmd.ExecuteNonQuery()

# SQL Server is 2000
if ($auditSMOServer.Information.VersionMajor -eq 8)
    {
		$UserDumpQueryText = "
SELECT
	ServerName				= @@SERVERNAME,
	LoginName				= AccessSummary.LoginName,
	LoginType				= CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,
	DatabaseName			= DB_NAME(),
	SelectAccess			= MAX(AccessSummary.SelectAccess),
	InsertAccess			= MAX(AccessSummary.InsertAccess),
	UpdateAccess			= MAX(AccessSummary.UpdateAccess),
	DeleteAccess			= MAX(AccessSummary.DeleteAccess),
	DBOAccess				= MAX(AccessSummary.DBOAccess),
	SysadminAccess			= MAX(AccessSummary.SysadminAccess)
FROM
	(
		/* Get logins with permissions */
		SELECT 
			LoginName				= sysusers.name,
			SelectAccess			= CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,
			InsertAccess			= CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,
			UpdateAccess			= CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,
			DeleteAccess			= CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,
			DBOAccess				= 0,
			SysadminAccess			= 0
		FROM dbo.sysobjects
		INNER JOIN dbo.sysprotects
			ON sysprotects.id = sysobjects.id
		INNER JOIN dbo.sysusers
			ON sysusers.uid = sysprotects.uid
		INNER JOIN master.dbo.syslogins AS syslogins
			ON syslogins.sid = sysusers.sid
		WHERE sysobjects.type IN ('U', 'V')
			AND sysusers.issqlrole = 0
			AND sysprotects.protecttype IN (204, 205)
			AND sysprotects.action IN (193, 195, 196, 197)
		UNION ALL
		/* Get group members with permissions */
		SELECT 
			LoginName				= sysusersMember.name,
			SelectAccess			= CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,
			InsertAccess			= CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,
			UpdateAccess			= CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,
			DeleteAccess			= CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,
			DBOAccess				= 0,
			SysadminAccess			= 0
		FROM dbo.sysobjects
		INNER JOIN dbo.sysprotects
			ON sysprotects.id = sysobjects.id
		INNER JOIN dbo.sysusers AS sysusersRole
			ON sysusersRole.uid = sysprotects.uid
		INNER JOIN dbo.sysmembers
			ON sysmembers.groupuid = sysusersRole.uid
		INNER JOIN dbo.sysusers AS sysusersMember
			ON sysusersMember.uid = sysmembers.memberuid
		INNER JOIN master.dbo.syslogins AS syslogins
			ON syslogins.sid = sysusersMember.sid
		WHERE sysobjects.type IN ('U', 'V')
			AND sysusersRole.issqlrole = 1
			AND sysusersRole.name NOT IN ('public')
			AND sysprotects.protecttype IN (204, 205)
			AND sysprotects.action IN (193, 195, 196, 197)
		UNION ALL
		/* Get users in db_owner, db_datareader and db_datawriter */
		SELECT
			LoginName				= syslogins.name,
			SelectAccess			= CASE WHEN sysusers.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,
			InsertAccess			= CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
			UpdateAccess			= CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
			DeleteAccess			= CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
			DBOAccess				= CASE WHEN sysusers.name IN ('db_owner') THEN 1 ELSE 0 END,
			SysadminAccess			= 0
		FROM dbo.sysusers
		INNER JOIN dbo.sysmembers
			ON sysmembers.groupuid = sysusers.uid
		INNER JOIN dbo.sysusers AS sysusersMember
			ON sysusersMember.uid = sysmembers.memberuid
		INNER JOIN master.dbo.syslogins AS syslogins
			ON syslogins.sid = sysusersMember.sid
		WHERE sysusers.name IN ('db_owner', 'db_datareader', 'db_datawriter')
		UNION ALL
		/* Get users in sysadmin */
		SELECT
			LoginName				= syslogins.name,
			SelectAccess			= 1,
			InsertAccess			= 1,
			UpdateAccess			= 1,
			DeleteAccess			= 1,
			DBOAccess				= 0,
			SysadminAccess			= 1
		FROM master.dbo.syslogins AS syslogins
		WHERE syslogins.sysadmin = 1
	) AS AccessSummary
INNER JOIN master.dbo.syslogins AS syslogins
	ON syslogins.loginname = AccessSummary.LoginName
WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT')
GROUP BY
	AccessSummary.LoginName,
	CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END
		"
    }
# SQL Server is 2005 or greater
elseif ($auditSMOServer.Information.VersionMajor -gt 8)
    {
		$UserDumpQueryText = "
SELECT
	ServerName				= @@SERVERNAME,
	LoginName				= AccessSummary.LoginName,
	LoginType				= CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,
	DatabaseName			= DB_NAME(),
	SelectAccess			= MAX(AccessSummary.SelectAccess),
	InsertAccess			= MAX(AccessSummary.InsertAccess),
	UpdateAccess			= MAX(AccessSummary.UpdateAccess),
	DeleteAccess			= MAX(AccessSummary.DeleteAccess),
	DBOAccess				= MAX(AccessSummary.DBOAccess),
	SysadminAccess			= MAX(AccessSummary.SysadminAccess)
FROM
	(
		/* Get logins with permissions */
		SELECT 
			LoginName				= sysDatabasePrincipal.name,
			SelectAccess			= CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,
			InsertAccess			= CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,
			UpdateAccess			= CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,
			DeleteAccess			= CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,
			DBOAccess				= 0,
			SysadminAccess			= 0
		FROM sys.database_permissions AS sysDatabasePermission
		INNER JOIN sys.database_principals AS sysDatabasePrincipal
			ON sysDatabasePrincipal.principal_id = sysDatabasePermission.grantee_principal_id
		INNER JOIN sys.server_principals AS sysServerPrincipal
			ON sysServerPrincipal.sid = sysDatabasePrincipal.sid
		WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'
			AND sysDatabasePrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')
			AND sysServerPrincipal.is_disabled = 0
		UNION ALL
		/* Get group members with permissions */
		SELECT 
			LoginName				= sysDatabasePrincipalMember.name,
			SelectAccess			= CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,
			InsertAccess			= CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,
			UpdateAccess			= CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,
			DeleteAccess			= CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,
			DBOAccess				= 0,
			SysadminAccess			= 0
		FROM sys.database_permissions AS sysDatabasePermission
		INNER JOIN sys.database_principals AS sysDatabasePrincipalRole
			ON sysDatabasePrincipalRole.principal_id = sysDatabasePermission.grantee_principal_id
		INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
			ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
		INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
			ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
		INNER JOIN sys.server_principals AS sysServerPrincipal
			ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
		WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'
			AND sysDatabasePrincipalRole.type_desc = 'DATABASE_ROLE'
			AND sysDatabasePrincipalRole.name  'public'
			AND sysDatabasePrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')
			AND sysServerPrincipal.is_disabled = 0
		UNION ALL
		/* Get users in db_owner, db_datareader and db_datawriter */
		SELECT
			LoginName				= sysServerPrincipal.name,
			SelectAccess			= CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,
			InsertAccess			= CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
			UpdateAccess			= CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
			DeleteAccess			= CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
			DBOAccess				= CASE WHEN sysDatabasePrincipalRole.name = 'db_owner' THEN 1 ELSE 0 END,
			SysadminAccess			= 0
		FROM sys.database_principals AS sysDatabasePrincipalRole
		INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
			ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
		INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
			ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
		INNER JOIN sys.server_principals AS sysServerPrincipal
			ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
		WHERE sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader', 'db_datawriter')
			AND sysServerPrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')
			AND sysServerPrincipal.is_disabled = 0
		UNION ALL
		/* Get users in sysadmin */
		SELECT
			LoginName				= sysServerPrincipalMember.name,
			SelectAccess			= 1,
			InsertAccess			= 1,
			UpdateAccess			= 1,
			DeleteAccess			= 1,
			DBOAccess				= 0,
			SysadminAccess			= 1
		FROM sys.server_principals AS sysServerPrincipalRole
		INNER JOIN sys.server_role_members AS sysServerRoleMember
			ON sysServerRoleMember.role_principal_id = sysServerPrincipalRole.principal_id
		INNER JOIN sys.server_principals AS sysServerPrincipalMember
			ON sysServerPrincipalMember.principal_id = sysServerRoleMember.member_principal_id
		WHERE sysServerPrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')
			AND sysServerPrincipalMember.is_disabled = 0
	) AS AccessSummary
INNER JOIN master.dbo.syslogins AS syslogins
	ON syslogins.loginname = AccessSummary.LoginName
WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT')
GROUP BY
	AccessSummary.LoginName,
	CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END
		"
    }
# Unknown or unexpected SQL Server version
else
    {
        $UserDumpQueryText = "Unsupported SQL Server version"
    }

# Version is good, let's proceed
if ($UserDumpQueryText -ne "Unsupported SQL Server version")
    {
        # Loop through each database on the SQL instance
        foreach ($dbAudit in $auditSMOserver.databases)
            {
                if ($dbAudit.IsAccessible)
                    {
                        # Connect to the database
                        $auditDBName = $dbAudit.Name
                        $auditConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$auditServerName; Initial Catalog=$auditDBName; Integrated Security=SSPI")
                        $auditConn.Open()
                        $auditCmd = $auditConn.CreateCommand()

                        # Run the permissions dump query against the current database
                        $auditCmd.CommandText = $UserDumpQueryText
                        $queryresults = $auditCmd.ExecuteReader()

                        # Loop through permission dump resultset, print each value
                        foreach ($row in $queryresults)
                            {
                                $insertServerName = $row["ServerName"]
                                $insertDatabaseName = $row["DatabaseName"]
                                $insertLoginName = $row["LoginName"]
                                $insertUserName = $row["LoginName"]
                                $insertFullName = ""
                                $insertSelect = $row["SelectAccess"]
                                $insertInsert = $row["InsertAccess"]
                                $insertUpdate = $row["UpdateAccess"]
                                $insertDelete = $row["DeleteAccess"]
                                $insertDBO = $row["DBOAccess"]
                                $insertSysadmin = $row["SysadminAccess"]

								if ($row["LoginType"] -eq "SQL_USER")
									{
										$targetCmd.CommandText = "
MERGE dbo.AuditDBLogin AS tgt
USING
    (
        SELECT
            ServerName              = '$insertServerName',
            Login                   = '$insertLoginName',
            Username                = '$insertUsername',
            FullName                = '$insertFullName',
            DatabaseName            = '$insertDatabaseName',
            SelectAccess            = $insertSelect,
            InsertAccess            = $insertInsert,
            UpdateAccess            = $insertUpdate,
            DeleteAccess            = $insertDelete,
            DBOAccess               = $insertDBO,
            SysadminAccess          = $insertSysadmin
    ) AS src (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)
        ON 
            (
                tgt.ServerName = src.ServerName
                AND tgt.Login = src.Login
                AND tgt.Username = src.Username
                AND tgt.FullName = src.FullName
                AND tgt.DatabaseName = src.DatabaseName
            )
    WHEN NOT MATCHED THEN
        INSERT (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)
            VALUES (src.ServerName, src.Login, src.Username, src.FullName, src.DatabaseName, src.SelectAccess, src.InsertAccess, src.UpdateAccess, src.DeleteAccess, src.DBOAccess, src.SysadminAccess)
    WHEN MATCHED THEN
        UPDATE
            SET
                SelectAccess        = CASE WHEN src.SelectAccess > tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,
                InsertAccess        = CASE WHEN src.InsertAccess > tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,
                UpdateAccess        = CASE WHEN src.UpdateAccess > tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,
                DeleteAccess        = CASE WHEN src.DeleteAccess > tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,
                DBOAccess           = CASE WHEN src.DBOAccess > tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,
                SysadminAccess      = CASE WHEN src.SysadminAccess > tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;
                                                        "
										$null = $targetCmd.ExecuteNonQuery()
									}
								elseif ($row["LoginType"] -eq "WINDOWS_LOGIN")
									{
										$ADUser = Get-QADUser $insertLoginName
										$insertFullName = $ADUser.name
										$insertUserName = $ADUser.ntaccountname

										$targetCmd.CommandText = "
MERGE dbo.AuditDBLogin AS tgt
USING
    (
        SELECT
            ServerName              = '$insertServerName',
            Login                   = '$insertLoginName',
            Username                = '$insertUsername',
            FullName                = '$insertFullName',
            DatabaseName            = '$insertDatabaseName',
            SelectAccess            = $insertSelect,
            InsertAccess            = $insertInsert,
            UpdateAccess            = $insertUpdate,
            DeleteAccess            = $insertDelete,
            DBOAccess               = $insertDBO,
            SysadminAccess          = $insertSysadmin
    ) AS src (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)
        ON 
            (
                tgt.ServerName = src.ServerName
                AND tgt.Login = src.Login
                AND tgt.Username = src.Username
                AND tgt.FullName = src.FullName
                AND tgt.DatabaseName = src.DatabaseName
            )
    WHEN NOT MATCHED THEN
        INSERT (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)
            VALUES (src.ServerName, src.Login, src.Username, src.FullName, src.DatabaseName, src.SelectAccess, src.InsertAccess, src.UpdateAccess, src.DeleteAccess, src.DBOAccess, src.SysadminAccess)
    WHEN MATCHED THEN
        UPDATE
            SET
                SelectAccess        = CASE WHEN src.SelectAccess > tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,
                InsertAccess        = CASE WHEN src.InsertAccess > tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,
                UpdateAccess        = CASE WHEN src.UpdateAccess > tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,
                DeleteAccess        = CASE WHEN src.DeleteAccess > tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,
                DBOAccess           = CASE WHEN src.DBOAccess > tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,
                SysadminAccess      = CASE WHEN src.SysadminAccess > tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;
                                                        "
										$null = $targetCmd.ExecuteNonQuery()
									}
								elseif ($row["LoginType"] -eq "WINDOWS_GROUP")
									{
										$groupMembers = Get-QADGroupMember $insertLoginName -Indirect
										foreach ($member in $groupMembers)
											{
												$insertFullName = $member.name
												$insertUserName = $member.ntaccountname

												$targetCmd.CommandText = "
MERGE dbo.AuditDBLogin AS tgt
USING
	(
		SELECT
			ServerName              = '$insertServerName',
			Login                   = '$insertLoginName',
			Username                = '$insertUsername',
			FullName                = '$insertFullName',
			DatabaseName            = '$insertDatabaseName',
			SelectAccess            = $insertSelect,
			InsertAccess            = $insertInsert,
			UpdateAccess            = $insertUpdate,
			DeleteAccess            = $insertDelete,
			DBOAccess               = $insertDBO,
			SysadminAccess          = $insertSysadmin
	) AS src (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)
		ON 
			(
				tgt.ServerName = src.ServerName
				AND tgt.Login = src.Login
				AND tgt.Username = src.Username
				AND tgt.FullName = src.FullName
				AND tgt.DatabaseName = src.DatabaseName
			)
	WHEN NOT MATCHED THEN
		INSERT (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)
			VALUES (src.ServerName, src.Login, src.Username, src.FullName, src.DatabaseName, src.SelectAccess, src.InsertAccess, src.UpdateAccess, src.DeleteAccess, src.DBOAccess, src.SysadminAccess)
	WHEN MATCHED THEN
		UPDATE
			SET
				SelectAccess        = CASE WHEN src.SelectAccess > tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,
				InsertAccess        = CASE WHEN src.InsertAccess > tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,
				UpdateAccess        = CASE WHEN src.UpdateAccess > tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,
				DeleteAccess        = CASE WHEN src.DeleteAccess > tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,
				DBOAccess           = CASE WHEN src.DBOAccess > tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,
				SysadminAccess      = CASE WHEN src.SysadminAccess > tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;
														"
												$null = $targetCmd.ExecuteNonQuery()

											}
									}
                            }
                    }
            }
    }

The post Automated Permissions Auditing With Powershell and T-SQL: Part 5 appeared first on RealSQLGuy.

Comments

Leave a comment on the original post [realsqlguy.com, opens in a new window]

Loading comments...