March 22, 2010 at 3:55 am
Hi All,
I have a situation whereby, one proc from DB1 calls other procs from DB2 etc, or a situation where a trigger in DB1 updates a table in DB2.
I know I can use execute as to get around the problem, but even that introduces another problem, I get this error.
Server: Msg 916, Level 14, State 1, Procedure crossdb, Line 2
The server principal "sa" is not able to access the database "DB_NAME"
under the current security context.
If I set cross database chaining on, I dont even need to use execute AS.
If I set database trustworthy ON, I can use execute as without any problem.
The other alternative is to use a certificate to sign the stored procedure, but it sounds rather complicated. And as I script out permissions to be applied to production boxes, the idea of certificates does make things really complicated. My question is, is there any other way around the problem or do i have to use certificates.
I know certificates are the best practice approach, but what are the disadvantages of using the other 2 methods I outlined above.
Also, I note that execute as can be quite dangerous as well. I cant believe the script below works.
create procedure sp_roguecode (@login varchar(100))
with execute as owner
as
begin
declare @sql varchar(100)
set @sql = '
drop user ' + @login
exec(@sql)
end
Any advice.
August 3, 2012 at 9:00 am
Did you ever get an answer?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply