User defined assemblies

Other SQL Server Topics
,
1

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
|Microsoft.SqlServer.Types|UNSAFE_ACCESS|
|StairwayToSQLCLR-02-Example|SAFE_ACCESS|

When I execute the same TSQL 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.

name permission_Set_Desc
---- -------------------
Microsoft.SqlServer.Types UNSAFE_ACCESS

So, I am unable to get the user defined assemblies from Powershell. Can you please help, If I miss something here?

2

Are you using the same credentials logging in to SSMS and PowerShell?

Also, are you querying the same database each time?

3

Hi Robert, I found the mistake that in SSMS, based on the session it connected to a specific database whereas in Powershell, I did not specify any database name to be connected, so I guess it connected to the default database..