Forum Discussion
hjvanamersfoort
Jul 10, 2024Copper Contributor
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;
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;
No RepliesBe the first to reply