We have two database instances on two separate server boxes that are linked. The website user needs to be able to run stored procedures on DatabaseA which query DatabaseB tables, however it should not be able to read directly from tables on DatabaseB.

Is such a thing possible in Sql Server 2005?

Simply creating a Stored Procedure and giving the web user permissions to execute it returns an error of Access to the remote server is denied because no login-mapping exists.

I really don't want to give the website user account free access to DatabaseB tables.


Aaron's suggestion of using EXECUTE AS OWNER does not work because Owner doesn't have access to DatabaseB.

Using EXECUTE AS 'UserWithPermissions' returns an error of Access to the remote server is denied because the current security context is not trusted. Looking this up online leads me to believe this is because the database is not marked as TRUSTWORTHY, so the remote database rejects the connection. I suspect it would work fine if the two database instances were on the same server.

I did not want to set the web database as TRUSTWORTHY since some of the security on it is more relaxed, so I went with Aaron's other option: Create a User on DatabaseB that only has access to a few specific views containing the data the website should access, then go into the Security Properties on the Linked Server object and setup the website user to login as the new limited user when accessing the linked server.

This allowed my website user to have limited access to specific data in the private database, without giving it access to any of the underlying tables.

2 Answers 2


Couldn't you have procedure A run with execute as owner or execute as 'db user tied to security account associated with linked server'? That way the rights of the caller don't have to transfer...

Or, you can create a login on the remote server that only has the ability to execute the queries you want, and add an impersonation account to the existing linked server on Server A to have the local webserver login impersonate the new login on server B.

  • Yeah but then the owner of the stored proc in Db1 would need to have permissions on the tables in Db2, right? I think this is a cross-db chaining issue. Commented Jun 18, 2012 at 18:30
  • Well it's across servers, so db chaining isn't really going to help (I've only ever seen db chaining used in the context of multiple databases within a single instance). If the linked server is setup to use an account that the caller can't login as, but has execute privileges on the remote procedure (ideal) or select privileges on the remote tables, this should isolate the remote database from the user and also allow for the local procedure to be executed. Commented Jun 18, 2012 at 18:33
  • I tried both EXECUTE AS OWNER and EXECUTE AS 'UserWithAccess' and am getting the same error. I'm fairly sure dbo (owner) doesn't have access to DatabaseB, and I was trying to figure out if I have the syntax wrong for executing as another user...
    – Rachel
    Commented Jun 18, 2012 at 18:34
  • Can you explain (or show a screen shot) of how your linked server is setup (most importantly the security context screen)? Commented Jun 18, 2012 at 18:35
  • Ah I didn't see the multiple servers portion. Good catch. Commented Jun 18, 2012 at 18:36

I prefer to solve this sort of problem with module signing:

use [<DatabaseName>];

create certificate <CertificateName>
    encryption by password = 'CorrectHorseBatteryStaple1!'
    with subject = 'Signing Certificate for Module: <Object>';

add signature to object::<Schema>.<Object>
    by certificate <CertificateName>
    with password = <CertificatePassword>;

grant execute on <Schema>.<Object>
    to <LoginName>;

backup certificate <CertificateName>
    to file = <Location>;

-- create certificate in master
use [master];

create certificate <CertificateName>
    from file = <Location>;

Alter the procedure to execute as owner and <LoginName> will be able to use it as intended without needing grants to underlying objects. You still have to handle cross-server permissions in some manner but this makes the cross-database portion easy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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