Cross database access SQL user permission

Question

From this link, I noted that it is enough to grant application user 'execute' permission to the stored procedure.

But it will not work when the procedure is accessing the objects from different database.

We can think of two possible solutions, which involve creating a SQL login per database and assign minimum set of permissions, and

1. Use execute as 'user' before actually accessing objects (could be stored procedures, tables, views or etc) in other databases. (i.e. RecordsUser before accessing Records database)
2. Create a linked server for each database and refers to that in required procedures.

Is there any other solutions other better approaches than these?

Answer 1

You are falling into the constrained execution model of 'execute as user'. This sand boxing is by design and has two possible solutions:

• mark database as trustworthy (not recommended)
• use code signing (recommended but difficult)

Read more:

• http://rusanu.com/2006/01/12/why-does-feature-not-work-under-activation/
• http://rusanu.com/2006/03/07/call-a-procedure-in-another-database-from-an-activated-procedure/
• https://msdn.microsoft.com/en-us/library/ms188304.aspx

Answer 2

Linked server is only needed if the query needs to access another server, however you don't need a linked server for accessing a separate database.

1. Be sure that the user exists in both Database1 and Database2
2. Be sure that the user has execute permissions on both Database1 and Database2 sprocs

You can grant execute permissions like so:

USE [Database1]
GRANT CONNECT TO [username];
GRANT EXEC ON [schema].[sp_name] TO [username];

USE [Database2]
GRANT CONNECT TO [username];
GRANT EXEC ON [schema].[sp_name] TO [username];