0

Right now I have two databases behind my asp.net web application. I'm using a single login to access both databases. the login has full ownership of one database and read-only rights to the second database. I'd like to set it up so that specific stored procedures from my main database can do update/delete operations on the second database. This way I can control the exact update/delete operations that are happening. I've been having a hard time figuring this one out, any advice would be appreciated.

0

You can create a second user with elevated priviliges and use WITH EXECUTE AS 'username' clause in the stored procedure body, having the procedure executed as the user with readonly permissions would be elevated to the second user who can perform modifications to the second database.

In order for this to work you will also have to grant IMPERSONATE permission to the readonly user and to set a trust relationship between databases - this can be done in at least one of two ways: ALTER DATABASE <dbname> SET TRUSTWORTHY ON (which might impose some security issues) or the second option to use certificates to authorize user. This link might be helpful (it's written in the context of Service Broker, but the idea is the same considering security): LINK

  • I ended up getting this working not long after posting this! Yes, I figured out how to enable the impersonation on a user and all is working well. I wasn't able to get it working with Execute as in the procedure header, I has to use EXECUTE AS LOGIN = 'loginname' and this worked as expected. I never ended uphaving to turn trustworthy on. – ErpaDerp Jun 27 '18 at 20:51
0

I'd like to set it up so that specific stored procedures from my main database can do update/delete operations on the second database.

Cross Database Ownership Chaining is what you need.

You should enable Cross Database Ownership Chaining on both your databases and ensure that the objects to modify in db2 have the same owner that the calling procedure.

Your user in the first db has already all the permissions it need in the first db, so he has execute on every procedure your write. Now, if the objects beeing modified in your procs have the same owner and both db have db_chaining enabled, the permissions on these objects won't be checked at all.

Here is a small repro where the principal test_login is db_owner in the first db db1 and db_datareader in the second db db2:

create login test_login with password = '*****', check_policy = off;

use db1;
create user test_login from login test_login;
exec sys.sp_addrolemember 'db_owner', 'test_login';

use db2;
create user test_login from login test_login;
exec sys.sp_addrolemember 'db_datareader', 'test_login';

create table dbo.tmp (id int identity);

use db1;
go
create proc dbo.usp_insert_into_db2 as
insert into db2.dbo.tmp default values;

execute as login = 'test_login';
exec dbo.usp_insert_into_db2;

--Msg 229, Level 14, State 5, Procedure usp_insert_into_db2, Line 2 [Batch Start Line 17]
--The INSERT permission was denied on the object 'tmp', database 'db2', schema 'dbo'.

revert;

alter database db1 set DB_CHAINING on;
alter database db2 set DB_CHAINING on;

execute as login = 'test_login';
exec dbo.usp_insert_into_db2;

So before enabling db_chaining on both databases, our test_login gets

The INSERT permission was denied

But after enabling it the insert through the procedure is successful because the owner of all the partecipating objects (sp in db1 and the table in db2) is the same, it's dbo.

One thing to mention here is that your db_owner can modify the data in other db by writing his own stored procedures or by modifying the sps written by you, so maybe it's the case to write your stored procedures in db2, and give the execute permission to your user in db2 on only these procedures. This way your user will be able to modify the data in db2 only through your stored procedures and he will not be able to modify/write his own procedures. In the last case you don't need db chaining, all you need is to write those procedures in db2 and give the execute permission on them to your user.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.