Forum Discussion

EdSpa290's avatar
EdSpa290
Copper Contributor
May 03, 2024

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 has EXECUTE permission on this stored procedure.

 

When user runs this stored procedure, it is failing with the error that says "the server principle "domainname\username" is not able to access the database "dbB" under the current security context"

 

Note that domainname\username clearly exists in database dbB, it is not disabled, and following sql proves it:

 

select * from dbB.sys.sysusers where name = 'domainname\username'

 

I know that dynamic sql within stored procedure changes security context of its execution, but I would expect that since this user exists in all dbs (including dbB) it should not be the problem.

 

Does anybody have any idea what could be the issue?

 

Thanks

 

    • EdSpa290's avatar
      EdSpa290
      Copper Contributor
      May 06, 2024

      olafhelper ,

       

      How is this related to linked servers?

       

      We're talking about several databases on the same sql server database instance.

  • hjvanamersfoort's avatar
    hjvanamersfoort
    Copper Contributor
    Jul 10, 2024

    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;

Resources