SQL Server how can I get current logged in username in Azure SQL database that allows only AD authentication?

vc9ivgsu 于 2023-03-22 发布在其他

关注(0)|答案(1)|浏览(155)

I want to define a default constraint on a table which shows that a specific user performed the DML operations on a table.

I define columns as below:

CreatedByUser varchar(50) default system_user

While running the query from the SSMS with my own Id - it gives me correct result i.e. my email ID

When I run the DML operations on the same table through ADF with linked service supporting MSI - I don't get name of the principal ( Data Factory name) - rather I get something like below:

Can someone please help here?

sql-server

来源：https://stackoverflow.com/questions/75748343/how-can-i-get-current-logged-in-username-in-azure-sql-database-that-allows-only

1条答案

按热度按时间

wlsrxk511#

I created a table with below columns

Create table MyTable (Id int, Name varchar(100),CreatedByUser varchar(50) default system_user )

I inserted some data into MyTabel through Azure SQL query panel and created stored procedure to run insert values into MyTable as below:

CREATE  PROCEDURE InsertData
@ID INT,
@Name VARCHAR(50)
AS
BEGIN
SET  NOCOUNT  ON;
DECLARE @CurrentUser VARCHAR(50);
SELECT @CurrentUser =  SUSER_SNAME();
INSERT  INTO MyTable (ID, Name, CreatedByUser)
VALUES (@ID, @Name, @CurrentUser);
END

I created ADF user and assigned roles to the user using below code:

create  user [<adfName>] from  external  provider
alter  role db_owner add  member [<adfName>]

I created linked service for Azure SQL database through MSI authentication.

I executed stored procedure with Id, Name parameters executed the pipeline.

when select data from MyTable with select * from [dbo].[MyTable] , I got below output.

I add new column to the table bu using alter table [dbo].[MyTable] add c_user varchar(50) and updated below stored procedure as below

CREATE  PROCEDURE InsertData
    @ID INT,
    @Name VARCHAR(50),
   @c_user VARCHAR(50)
    AS
    BEGIN
    SET  NOCOUNT  ON;
    DECLARE @CurrentUser VARCHAR(50);
    SELECT @CurrentUser =  SUSER_SNAME();
    INSERT  INTO MyTable (ID, Name, CreatedByUser)
    VALUES (@ID, @Name, @CurrentUser,@c_user);
    END

I added dynamic content as @pipeline().DataFactory in c_user parameter in stored procedure parameters and executed it.