Find users under a specific database role for all the servers in my environment

[Chris-Schmidt-MSFT]

First published on MSDN on Aug 11, 2016
Recently I was working with a customer who wanted to know all the users in their environment who had ssis_admin permissions within their Integration Services environment. In SQL Server 2008 R2, a new catalog view was introduced that allows us to bridge between database_roles and their associated members, under sys.database_role_members. With this simple bridge table, we can do some quick joins to the sys.database_principals catalog view and get a nice report of who has permissions under a specific database role. Here is a very straight forward SQL query that allows us to see all the users who have ssis_admin on our server:

SELECT dr.name [DatabaseRoleName]
	,ISNULL(pr.Name, 'No Members Found!') [DatabaseUserName]
FROM sys.database_role_members drm
RIGHT JOIN sys.database_principals dr ON drm.role_principal_id = dr.principal_id
LEFT JOIN sys.database_principals pr ON drm.member_principal_id = pr.principal_id
WHERE dr.name = 'ssis_admin'

You could easily change this to pull for any database role on any database. In this example I'm using it for ssis_admin under the SSISDB database. We can extend this in order to loop over a list of all servers in the environment by using PowerShell. In my environment, I have a centralized admin database that contains a list of all the servers that I want to track in my lab. I can query this from PowerShell by using Invoke-sqlcmd:

#list of servers to be check 
$servers = Invoke-Sqlcmd -ServerInstance sql2016a -Database PerformanceMonitor -Query "SELECT DISTINCT [ServerName] FROM Server WHERE HasIsCatalog = 1" 

Next, we are going to wrap the actual sql statement we want to run inside of a function:

#function to retrieve database users and roles</pre> 
Function Get-DatabaseUsers ([string]$Servername) 
{ 
	$query = Invoke-Sqlcmd -ServerInstance $Servername -Database SSISDB -Query "SELECT @@SERVERNAME [ServerName]
	,dr.name [DatabaseRoleName] 
	,ISNULL(pr.Name, 'No Members Found!') [DatabaseUserName] 
	FROM sys.database_role_members drm 
	RIGHT JOIN sys.database_principals dr 
	ON drm.role_principal_id=dr.principal_id 
	LEFT JOIN sys.database_principals pr 
	ON drm.member_principal_id=pr.principal_id 
	WHERE dr.name = 'ssis_admin'" 

	#the below outputs the result of the above when the function is called 
	$query 
}

The function itself is relatively straight-forward. It takes the query we wrote earlier in this blog, calls it from invoke-sqlcmd, and uses a parameter called $Servername as an input to know which server to run against. The bottom $query statement outputs the result of the invoke-sqlcmd from calling the function.

Next on our list is to write the result into our centralized admin server. One of the easiest ways to do this in PowerShell is to use a datatable and then bulk copy the data into the table. We need to set the stage by creating a function:

Function out-DataTable 
{ 

	$dt = new-object Data.datatable 

	$First = $true 
	foreach ($item in $input){ 
		$DR = $DT.NewRow() 

		$Item.PsObject.get_properties() | foreach { 
			if ($first) {
				$Col = new-object Data.DataColumn 
				$Col.ColumnName = $_.Name.ToString() 
				$DT.Columns.Add($Col)
			} 

			if ($_.value -eq $null) { 
				$DR.Item($_.Name) = "[empty]" 
			} 
			elseif ($_.IsArray) { 
				$DR.Item($_.Name) =[string]::Join($_.value ,";") 
			} 
			else { 
				$DR.Item($_.Name) = $_.value 
			} 
		} 

		$DT.Rows.Add($DR) 

		$First = $false 
	} 
	return @(,($dt))
}

And then we can loop over the list of servers in our environment that we want to manage and write the result to our data table.

#execute the functions 
foreach ($ServerName in $servers) 
{ 
	$dataTable = Get-DatabaseUsers $ServerName.ServerName | out-DataTable 
	#$dataTable #you can use this to check the output 

Ensuring that we're getting the results we expect, now we can write the result to a sql table. We'll do this by using the Data.sqlclient.sqlbulkcopy object and mapping it to a predefined destination table. The below code segment handles this part of the process:

#initialize a sqlbulk copy object to write the data to the centralized table 

$connectionString = "Data Source=sql2016a; Integrated Security=True;Initial Catalog=PerformanceMonitor;" 

#open the connection 

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString 

#set the destination table 

$bulkCopy.DestinationTableName = "DatabaseRolesList" 

#perform the column mappings 

$bulkCopy.ColumnMappings.Add("ServerName","ServerName") 

$bulkcopy.ColumnMappings.Add("DatabaseRoleName","DatabaseRoleName") 

$bulkcopy.ColumnMappings.Add("DatabaseUserName","DatabaseUserName") 

#write the data to the table 

$bulkCopy.WriteToServer($dataTable) 

The nice part about this script is that by simply changing the query in the invoke-sqlcmd function and doing the bulk copy mapping in the foreach loop, this script could be extended to run almost any sql query and write the results to a sql table.

An end to end sample of the code is available on the TechNet gallery here.