Security context changed between creating login and user within ADO deployment Synapse lakedatabase

Hello,

Within a DevOps pipeline I have a sql script which creates a login and a user on a Synapse lakedatabase. Separately both scripts are working fine but not combined in one script because of the security context is changing after executing 'EXEC (@runSql)':

##[error]The server principal "***@xxxx" is not able to access the database "lakedatabase" under the current security context.

After executing this script twice, 'EXEC (@runSql)' is not executed this second time because the login exists, the security context is not changed and the user is created succesfully too.

I can split up these script in two scripts, but I do not want this. Is there a way after executing EXEC (@runSql) to switch back to the original security context? Another solution is also welcome.

This sql script is called within a yaml/powershell script which extract @password from key vault.

USE MASTER;

DECLARE @runSql NVARCHAR(200);

DECLARE @password NVARCHAR(80) = '$(password)';

IF NOT EXISTS

(

SELECT 1

FROM sys.server_principals

WHERE [name] = 'xxx'

)

BEGIN

SET @runSql = 'CREATE LOGIN [xxx] WITH PASSWORD = '''+ @password+''''

EXEC (@runSql)

END;

REVERT

USE [lakedatabase];

IF NOT EXISTS

(

SELECT 1

FROM sys.database_principals

WHERE [type] = 'S'

AND [name] = 'xxx'

)

BEGIN

CREATE USER [xxx] FROM LOGIN [xxx]

ALTER ROLE [db_datareader] ADD MEMBER [xxx]

END;

azure

Forum Discussion

Security context changed between creating login and user within ADO deployment Synapse lakedatabase

Share

Resources