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

Copper Contributor

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;
 
GO
 
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;
 
GO
 
REVERT
 
GO
 
USE [lakedatabase];
 
GO
 
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;
0 Replies