I have the following issue:

I have two different databases, db1 and db2. I have an application that loads data into db2 or db3. db1 has a few tables that the application uses to determine behavior, including which db the application should load data into.

Users need to have write access to db1 to operate the application (there is a console application that writes to tables in db1, operating with windows authentication).

Users should not have DML privileges to db2 and db3, with the exception of a few predetermined operations. We grant AD groups database roles to control access from and organization perspective. Specifically, I'm trying to build a stored procedure in db1 that operators can use to reverse data loaded to db2 or db3 with appropriate logging.

I'm attempting to use create proc ... execute as owner to accomplish this, but it does not seem to be working when I try to hit tables in db2/db3 (I'm thinking that "execute as owner" operates on db level users an not server level logins?). The following causes a permission error stating that the owner (myself) does not have permissions to db2/db3.

use db1
go

create proc dbo.wrapper @recordid int 
as begin 
/*
  capturing user
*/
declare @usr varchar(255) = SUSER_SNAME()
exec dbo.inner @usr , @recordid 
end 

use db1
go

create proc dbo.inner @usr varchar(255), @recordid int
with execute as owner 
as begin 
    /*
       logic to determine whether to update db2 or db3 goes here
    */
    insert db2.rolled_back 
    select * , @usr from db2.transactions where id = @recordid 

    delete from db2.transactions where id = @recordid 

    insert db3.rolled_back 
    select * , @usr from db3.transactions where id = @recordid 

    delete from db3.transactions where id = @recordid 

end

Is there a way to get this to work? I've heard that certificate signing could do this, does anyone have any experience using certificate users. Our DBA's would rather not have to maintain certificates, so if there is a way to get this to work without certificates that would be best.

Any advice would be helpful.

Thank You!

CodePudding user response：

I'm going to cover the cross database chaining side of thing here. note that there are certainly security considerations when using this method. For example someone with permissions to create objects in one database can give themselves access to data in another database with the owner, when they themselves have no access to the other database. The security concerns, however, are out of scope of this answer.

Firstly, let's create a couple of test databases.

USE master;
GO

CREATE DATABASE Chain1;
CREATE DATABASE Chain2;

Now I'm going to CREATE a LOGIN, which is disable and make that the owner of these 2 databases. The databases having the same owner is important, as otherwise the chaining won't work.

CREATE LOGIN ChainerOwner WITH PASSWORD = N'SomeSecurePassword123';

ALTER LOGIN ChainerOwner DISABLE;
GO
ALTER AUTHORIZATION ON DATABASE::Chain1 TO ChainerOwner;
ALTER AUTHORIZATION ON DATABASE::Chain2 TO ChainerOwner;

I'm also going to create a LOGIN which we're going to use to test on:

CREATE LOGIN SomeUser WITH PASSWORD = N'SomeSecurePassword123';

Great, now I can create a few objects; a table in Chain1, a PROCEDURE in Chain2 that accesses the TABLE, and a USER in both databases for SomeUser. In Chain1 the USER will be given no permissions, and in Chain2 the user will be given the permsision to EXECUTE the PROCEDURE:

USE Chain1;
GO
CREATE TABLE dbo.SomeTable (I int IDENTITY,
                            S varchar(10));
INSERT INTO dbo.SomeTable (S)
VALUES ('abc'),
       ('xyz');
GO
CREATE USER SomeUser FOR LOGIN SomeUser;
GO
USE Chain2;
GO
CREATE PROC dbo.CrossDBProc @I int AS
BEGIN
    SELECT I,
           S
    FROM Chain1.dbo.SomeTable
    WHERE I = @I;
END;
GO
CREATE USER SomeUser FOR LOGIN SomeUser;
GO
GRANT EXECUTE ON dbo.CrossDBProc TO SomeUser;
GO

Great, all the objects are created, now let's try to EXECUTE that PROCEDURE:

EXECUTE AS LOGIN = 'SomeUser';
GO
EXEC dbo.CrossDBProc 1; --This fails
GO
REVERT;
GO

This fails, with a permission error:

The SELECT permission was denied on the object 'SomeTable', database 'Chain1', schema 'dbo'.

This is expected, as there is no ownership chaining. let's, therefore enable that now.

USE master;
GO

ALTER DATABASE Chain1 SET DB_CHAINING ON;
ALTER DATABASE Chain2 SET DB_CHAINING ON;

Now if I try the same again, the same SQL works:

USE Chain2;
GO
EXECUTE AS LOGIN = 'SomeUser';
GO
EXEC dbo.CrossDBProc 1; --This now works
GO
REVERT;
GO

This successfully returns the result set

So, yes you can chain cross database, but it requires some set up, and (again) there are security considerations you need think about.

Clean up:

USE master;
GO
DROP DATABASE Chain1;
DROP DATABASE Chain2;
GO
DROP LOGIN ChainerOwner;
DROP LOGIN SomeUser;