How do I get users created 'FROM EXTERNAL PROVIDER'

[ClaudioESSilva]

Hi,

When using $db.Users (which returns [Microsoft.SqlServer.Management.Smo.User]) I can't get the ones that were created using EXTERNAL PROVIDER

Ex:

CREATE USER [blah@****.onmicrosoft.com] FROM EXTERNAL PROVIDER

My understanding is that it doesn't show because issqluser (SQL Server) property (from sys.sysusers) is 0 (False)

From sys.database_principals it shows as EXTERNAL_USER

Does SMO expose this type of user?
How can we get them using SMO?

[shueybubbles]

What type of server are you connected to? SMO is likely out of date for external users on SQL Managed Instance, since it only checks for external users on Azure DB. See

sqlmanagementobjects/src/Microsoft/SqlServer/Management/SqlEnum/xml/user.xml

Line 77 in 9809360

<version cloud_min_major='12' datawarehouse_enabled='true'>

Users are found using sys.database_principals

[ClaudioESSilva]

Query got cropped on the LEFT OUTER JOIN line.
I think it's COLLATE something. Can you edit the snippet?

[shueybubbles]

CO is the type column value in sys.database_permissions
eg

class	class_desc	major_id	minor_id	grantee_principal_id	grantor_principal_id	type	permission_name	state	state_desc
0	DATABASE	0	0	1	1	CO  	CONNECT	G	GRANT

[ClaudioESSilva]

this bit is correct? 🤔

Should be 'CO' now I get it!

[shueybubbles]

sorry, the output window in SSMS hides a lot of complexity. The real query ends up being dynamic SQL

exec sp_executesql N'SELECT
u.name AS [Name],
''Server[@Name='' + quotename(CAST(
        serverproperty(N''Servername'')
       AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/User[@Name='' + quotename(u.name,'''''''') + '']'' AS [Urn],
u.create_date AS [CreateDate],
u.principal_id AS [ID],
CAST(CASE dp.state WHEN N''G'' THEN 1 WHEN ''W'' THEN 1 ELSE 0 END AS bit) AS [HasDBAccess]
FROM
sys.database_principals AS u
LEFT OUTER JOIN sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id and dp.type = @_msparam_0
WHERE
(u.type in (''U'', ''S'', ''G'', ''C'', ''K'' ,''E'', ''X''))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'CO'

[ClaudioESSilva]

Thanks @shueybubbles !
The query returns the 'E' as expected.

Found the problem

I can also see it from dbatools (SMO).
It's a cache/lack of refresh problem after adding the user (using TSQL and not refreshing the collection)

If I reconnect, to refresh my SMO.Server, object then it already appears.

Sorry about the confusion generated 😅 and thanks for the quick support!