SSC-FDM-TS0003

XP_LOGININFO mapped to custom UDF

Description

This message is shown when the XP_LOGININFO procedure is executed and returns the following set of columns (See SQL SERVER documentation for more info)

account name	type	privilege	mapped login name	permission path

To replicate this behavior, there is a query that select the columns from the APPLICABLE_ROLES view in Snowflake, which returns the following set of columns (See SnowFlake documentation for more info)

GRANTEE	ROLE_NAME	ROLE_OWNER	IS_GRANTABLE

SQL Server original columns are mapped as shown in the next table. They may be not completely equivalent.

SQL Server	SnowFlake
account name	GRANTEE
type	ROLE_OWNER
privilege	ROLE_NAME
mapped login name	GRANTEE
permission path	NULL

Example code

Input code:

IN -> SqlServer_01.sql

EXEC xp_logininfo

EXEC xp_logininfo 'USERNAME'

Output code:

OUT -> SqlServer_01.sql

--** SSC-FDM-TS0003 - XP_LOGININFO MAPPED TO CUSTOM UDF XP_LOGININFO_UDF AND MIGHT HAVE DIFFERENT BEHAVIOR **
SELECT
*
FROM
TABLE(XP_LOGININFO_UDF());

--** SSC-FDM-TS0003 - XP_LOGININFO MAPPED TO CUSTOM UDF XP_LOGININFO_UDF AND MIGHT HAVE DIFFERENT BEHAVIOR **
SELECT
*
FROM
TABLE(XP_LOGININFO_UDF('USERNAME'));

Recommendations

• If you need more support, you can email us at [email protected]