user/login impersonation extending beyond the current database

A stored procedure utilizing execute as owner (database owner = sa) is unable to call an procedure within another database whose owner is also sa.

The reason for this is that security impersonation is only valid within the database that the impersonation is performed. Any calls external to the database will result in an error.

Consider the following scenario: 2 databases, 2 different owners in the same instance. db1 owner creates db2 owner as a user within db1. He then issues an EXCUTE AS USER = ‘db2_owner’ command. If external database calls were allowed he would have full admin to db2, clearly not desirable.

To extend impersonation beyond the database you need to let the instance know that teh dataabse is trustworthy.

You can do this: ALTER DATABASE xxx SET TRUSTWORTHY ON

see https://msdn.microsoft.com/en-us/library/ms188304(v=sql.105).aspx for further information

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: