Forum Discussion
Dynamic sql within stored procedure security context
Hello, We have stored procedure in database dbA, that runs (among other things) dynamic sql. It accesses several dbs, not just the one where stored procedure is (dbA). Db user domainname\username...
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)'. 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;