Error while "execute as" in a procedure to query table in another database on same server

Question

I need assistance executing a procedure inside one database to query another database on the same SQL Server 2019 instance. I can run the procedure fine as myself, but I can't use execute as in either of the three places I have tried (commented out below).

The error I get is:

Cannot execute as the server principal because the principal "Domain\ServiceAcctIn_GroupName" does not exist, this type of principal cannot be impersonated, or you do not have permission.

OR if I execute as Owner I get:

The server principal "saName" is not able to access the database "SourceDB" under the current security context.

My setup: I'm on a Windows AD domain, my service account is in a group that I have granted login to the SQL Server and created a database user for the group on both databases. I have also set DB_Chaining to ON on both databases.

I need to use my service account to query the data because I cannot add all users to the same group the service account is in. They will however be in a different domain group with a role to select and execute this procedure in the [WorkingDB].

Here is my sample code:

USE master;
ALTER DATABASE [WorkingDB] SET DB_CHAINING ON;
ALTER DATABASE [SourceDB] SET DB_CHAINING ON;
GO

IF (NOT EXISTS (SELECT 1 FROM sys.server_principals AS sp WHERE (sp.[name] = 'Domain\GroupName')))
    CREATE LOGIN [Domain\GroupName] FROM WINDOWS;
GO

USE [SourceDB];
GO

EXEC sp_changedbowner 'saName';

IF (NOT EXISTS (SELECT 1 FROM sys.database_principals AS dp WHERE (dp.[name] = 'Domain\GroupName')))
    CREATE USER [Domain\GroupName] FOR LOGIN [Domain\GroupName];

GRANT CONNECT TO [Domain\GroupName];

IF (DATABASE_PRINCIPAL_ID('SystemName_ReadOnlyRole') IS NULL)
    CREATE ROLE SystemName_ReadOnlyRole AUTHORIZATION dbo;

GRANT EXECUTE TO SystemName_ReadOnlyRole;

ALTER ROLE db_datareader ADD MEMBER SystemName_ReadOnlyRole;
ALTER ROLE SystemName_ReadOnlyRole ADD MEMBER [Domain\GroupName];
GO

USE [WorkingDB];
GO

EXEC sp_changedbowner 'saName';

IF (NOT EXISTS (SELECT 1 FROM sys.database_principals AS dp WHERE (dp.[name] = 'Domain\GroupName')))
    CREATE USER [Domain\GroupName] FOR LOGIN [Domain\GroupName];

ALTER ROLE db_owner ADD MEMBER [Domain\GroupName];
GO

CREATE OR ALTER PROCEDURE MySchema.GetDataFromSourceDB
--WITH EXECUTE AS N'Domain\UserInGroupName'
--WITH EXECUTE AS OWNER
AS
BEGIN
    --EXECUTE AS LOGIN = N'Domain\ServiceAcctIn_GroupName';
    --EXECUTE AS USER = N'Domain\ServiceAcctIn_GroupName';
    --EXEC ('SELECT TOP(10) * FROM [SourceDB].[dbo].[TableName];');
    SELECT TOP(10) * FROM [SourceDB].[dbo].[TableName];
END
GO

--EXECUTE AS LOGIN = N'Domain\ServiceAcctIn_GroupName';
EXEC MySchema.GetDataFromSourceDB;
--REVERT;

/*
Error:
Cannot execute as the server principal because the principal "Domain\ServiceAcctIn_GroupName" does not exist, this type of principal cannot be impersonated, or you do not have permission.
OR
The server principal "saName" is not able to access the database "FIMSynchronizationService" under the current security context.
*/

Hmm, if you remove all execute as owner etc and just select the data from another db via a view in your procedure, it should work with chaining if the user can call the original procedure and have view access. Or what error are you getting? — siggemannen
– siggemannen, Commented Mar 31 at 22:37
I’m not very familiar with signing, but this MS guide doesn’t specify which database I should create the signed certificate and certificate user and can I put that execute as inside my procedure on the WorkingDB? learn.microsoft.com/en-us/sql/relational-databases/… — Paul Young
– Paul Young, Commented Apr 1 at 1:27
You can actually tell by the USE statements, @PaulYoung ; you create the cert in both database, sign the procedure in its database (you can only do that in it's database), and create the certification user in the other database. — Thom A
– Thom A ♦, Commented Apr 1 at 9:05
by default, all databases have public role, which will show the database, but does not show its content. If you want to just be able to view tables or views, you need a server role that is shared between your desired databases (similar to public role) with read permissions. then assign members to this role, and they will have access to those databases as well (depends on your permissions setup). — iSR5
– iSR5, Commented Apr 1 at 9:11

Paul Young · Accepted Answer · 2025-04-01 19:23:49Z

Here is my working solution based on using certificates rather than DB_CHAINING. I'm not certain of the exact magic by just creating a user in the target DB with the certificate, but it is working as intended.

This is pseudo code, so there may be a typo or two.

/*
-- This pseudo code demonstrates a safe way to allow a procedure in one database to query data from another database on the same server.
-- [SourceDB] is where the desired table is.
-- [MyDatabase] is where the procedure is (using EXECUTE AS is optional as long as the user is part of the AD group).
-- NOTE: all CREATE commands should be inside IF statements to ensure proper state.
*/

--------------------------------------------------------------------------------
PRINT 'Step 1: prepare the server (database and accounts) (performed by SA):';
--------------------------------------------------------------------------------
USE [master];
SET NOCOUNT, ANSI_NULLS, QUOTED_IDENTIFIER, XACT_ABORT ON;
GO
ALTER DATABASE [SourceDB]   SET DB_CHAINING OFF;
ALTER DATABASE [MyDatabase] SET DB_CHAINING OFF;
CREATE LOGIN [SVC_MyDatabaseReadOnlyUser] WITH
     PASSWORD         = 'SupperChallengingPassword12@3'  -- Can be changed; never actually used for login; just impersonation.
    ,CHECK_EXPIRATION = OFF
    ,DEFAULT_DATABASE = [MyDatabase];
CREATE LOGIN [MyDomain\MyDatabaseAllUsersGroup] FROM WINDOWS WITH
    DEFAULT_DATABASE = [MyDatabase];
GRANT IMPERSONATE ON LOGIN::[SVC_MyDatabaseReadOnlyUser] TO [MyDomain\MyDatabaseAllUsersGroup]; -- WITH GRANT OPTION;
PRINT 'Done.';
GO


--------------------------------------------------------------------------------
PRINT 'Step 2: prepare the [WorkingDB] (performed by SA or db_owner):';
--------------------------------------------------------------------------------
USE [MyDatabase];
GO
-- Create users for [MyDatabase].  --
CREATE USER [MyDomain\MyDatabaseAllUsersGroup] FOR LOGIN [MyDomain\MyDatabaseAllUsersGroup] WITH DEFAULT_SCHEMA = [dbo];
CREATE USER [SVC_MyDatabaseReadOnlyUser]       FOR LOGIN [SVC_MyDatabaseReadOnlyUser]       WITH DEFAULT_SCHEMA = [dbo];
CREATE USER [MyDB_Steward]                     WITHOUT LOGIN                                WITH DEFAULT_SCHEMA = [dbo];
-- Create Roles and assign users --
CREATE ROLE [Role_MyDatabase_ReadOnly]  AUTHORIZATION [dbo];
GRANT SELECT  ON SCHEMA::[MySchema]     TO [Role_MyDatabase_ReadOnly];
GRANT EXECUTE ON SCHEMA::[MySchema]     TO [Role_MyDatabase_ReadOnly];
CREATE ROLE [Role_Steward]              AUTHORIZATION [dbo];
ALTER ROLE [db_datareader]              ADD MEMBER [Role_Steward];  
ALTER ROLE [db_datawriter]              ADD MEMBER [Role_Steward];
GRANT ALTER   ON SCHEMA::[MySchema]     TO [Role_Steward]; -- required for TRUNCATE TABLE;
GRANT EXECUTE ON DATABASE::[MyDatabase] TO [Role_Steward];
ALTER ROLE  [Role_MyDatabase_ReadOnly]  ADD MEMBER [MyDomain\MyDatabaseAllUsersGroup];
ALTER ROLE  [Role_MyDatabase_ReadOnly]  ADD MEMBER [SVC_MyDatabaseReadOnlyUser];
ALTER ROLE  [Role_Steward]              ADD MEMBER [MyDB_Steward];
GRANT EXECUTE TO [Role_MyDatabase_ReadOnly];
GRANT EXECUTE TO [Role_Steward];
GO
CREATE CERTIFICATE [Cert_MyDatabase]
    AUTHORIZATION [dbo]
    ENCRYPTION BY PASSWORD = 'AnotherChallengingPassword1@3' -- Will be needed whenever SP is altered.
    WITH SUBJECT = 'MyDatabase Certificate for reading from SourceDB',
    EXPIRY_DATE = '20991231'; -- Will not expire in my lifetime.
GO
CREATE OR ALTER PROCEDURE [MySchema].[GetDataFromSourceDB]
WITH EXECUTE AS N'MyDB_Steward'
AS
BEGIN
    SELECT ORIGINAL_LOGIN() AS LoginName, USER_NAME() AS UserName, Is_RoleMember('db_owner') AS IsDBO;
    EXECUTE AS CALLER; -- All users in [MyDomain\MyDatabaseAllUsersGroup] should succeed with query to [SourceDB].
    SELECT ORIGINAL_LOGIN() AS LoginName, USER_NAME() AS UserName, Is_RoleMember('db_owner') AS IsDBO;
    SELECT TOP(10) * FROM [SourceDB].[dbo].[SourceDBTable];
    REVERT;
    -- ToDo: process results of query...
END
GO
ADD SIGNATURE TO [MySchema].[GetDataFromSourceDB]
    BY CERTIFICATE [Cert_MyDatabase]
    WITH PASSWORD = 'AnotherChallengingPassword1@3';
PRINT 'Done.';
GO


-- Step 3: prepare the [SourceDB] --
--------------------------------------------------------------------------------
PRINT 'Step 3: prepare the [SourceDB] (performed by SA or db_owner):';
--------------------------------------------------------------------------------
USE [SourceDB];
GO
-- Create users for [SourceDB].  --
CREATE USER [MyDomain\MyDatabaseAllUsersGroup] FOR LOGIN [MyDomain\MyDatabaseAllUsersGroup] WITH DEFAULT_SCHEMA = [dbo];
CREATE USER [SVC_MyDatabaseReadOnlyUser]       FOR LOGIN [SVC_MyDatabaseReadOnlyUser]       WITH DEFAULT_SCHEMA = [dbo];
-- Create Roles and assign users --
CREATE ROLE [Role_MyDatabase_ReadOnly]  AUTHORIZATION [dbo];
ALTER ROLE [db_datareader]              ADD MEMBER [Role_MyDatabase_ReadOnly];
GRANT EXECUTE ON DATABASE::[SourceDB]   TO [Role_MyDatabase_ReadOnly];
ALTER ROLE  [Role_MyDatabase_ReadOnly]  ADD MEMBER [MyDomain\MyDatabaseAllUsersGroup];
ALTER ROLE  [Role_MyDatabase_ReadOnly]  ADD MEMBER [SVC_MyDatabaseReadOnlyUser];
PRINT 'Done.';
GO
--NOTE:  At this state, all users in [MyDomain\MyDatabaseAllUsersGroup] can use and query from [SourceDB].


--------------------------------------------------------------------------------
PRINT 'Step 4: Import [MyDatabase] Certificate into [SourceDB] (public key only) (performed by SA or db_owner):';
--------------------------------------------------------------------------------
USE [SourceDB];
GO
IF (EXISTS (SELECT 1 FROM sys.certificates AS c WHERE (c.[name] = N'Cert_MyDatabase')))
    DROP CERTIFICATE [Cert_MyDatabase];
GO
USE [MyDatabase]
GO
IF (EXISTS (SELECT 1 FROM sys.certificates AS c WHERE (c.[name] = N'Cert_MyDatabase')))
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'USE [SourceDB];
    CREATE CERTIFICATE [Cert_MyDatabase] AUTHORIZATION [dbo] FROM BINARY = '
      + CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'Cert_MyDatabase')), 1)
      + N';'
    --PRINT @SQL;
    EXEC (@SQL);
    PRINT 'Done.';
END
ELSE PRINT 'No Certificate found [Cert_MyDatabase] in [MyDatabase].';
GO


--------------------------------------------------------------------------------
PRINT 'Step 5: Create Certificate User in [SourceDB] (performed by SA or db_owner):';
--------------------------------------------------------------------------------
USE [SourceDB];
GO
IF (EXISTS (SELECT 1 FROM sys.certificates AS c WHERE (c.[name] = N'Cert_MyDatabase')))
BEGIN
    CREATE USER [SVC_MyDatabaseCertificateUser]
        FROM CERTIFICATE [Cert_DataQuality];
    ALTER ROLE [Role_MyDatabase_ReadOnly] ADD MEMBER [SVC_MyDatabaseCertificateUser];
    PRINT 'Done.';
END
ELSE PRINT 'No Certificate found [Cert_MyDatabase] in [SourceDB].';
GO


--------------------------------------------------------------------------------
PRINT 'Step 6: Testing (performed by any user in [MyDomain\MyDatabaseAllUsersGroup]):';
-- NOTE: When altering the Procedure in [MyDatabase], you must re-sign it like in step 2 above).
--------------------------------------------------------------------------------
USE [MyDatabase];
GO
--EXECUTE AS LOGIN = 'SVC_MyDatabaseReadOnlyUser';  -- Success!
EXEC [MySchema].[GetDataFromSourceDB];  -- Success!
--REVERT;

Collectives™ on Stack Overflow

Error while "execute as" in a procedure to query table in another database on same server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related