CODEWITHSUNDEEP
sql-serverstored-procedurespermissionssql-server-2012
Moh Moh Oo
Moh Moh Oo

Reputation: 279

Cross database access SQL user permission

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?

Upvotes: 2

Views: 1779

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294267

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:

Upvotes: 3

Tigerjz32
Tigerjz32

Reputation: 4472

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];

Upvotes: 0

Related Questions