I am trying to extract list of user defined assemblies through PowerShell for one of SQL Server administration for automation. When I open SSMS and execute this query as

Select name, permission_set_desc 
From sys.assemblies

I am able to get the output as 2 rows as below

When I execute the same T-SQL through PowerShell using Invoke-SQLCMD command, I do not get any user defined assemblies rather only system defined assembly.

This is the command I used in PowerShell:

$query = "Select name, permission_Set_Desc from sys.assemblies"
Invoke-Sqlcmd -Query $query -AbortOnError -OutputSQLErrors $true

I get the below only which is system defined assembly.

I am unable to get the user defined assemblies from PowerShell.

Did I miss something here?

CodePudding user response：

As @Vijayanand A said, assemblies are defined at database level. However, you can iterate all databases and display all of them with attached assemblies:

DECLARE @sql nvarchar(MAX) = '';

SELECT @sql  = 'SELECT ' 
    QUOTENAME(DB.Name, '''')   ' COLLATE database_default db, ' 
    'name COLLATE database_default name, ' 
    'permission_set_desc COLLATE database_default permission_set_desc ' 
  'FROM '   QUOTENAME(DB.Name)   '.sys.assemblies UNION ALL '
FROM sys.databases DB

SET @sql = LEFT(@sql, LEN(@sql)-LEN(' UNION ALL'))

EXEC(@sql)