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.
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'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
db_owner in the first db
db_datareader in the second db
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
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
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.