Access another database from sp

I've looked around on how to do this and haven't had any luck so far. I have a stored procedure that needs to update tables in the current database (db1) and in another database (db2) on the same server. The user executing the stored procedure on db1 does not have permissions to access db2, but does know the username, password, and connection string of db2. Is there any way to create a connection to db2 in the stored procedure using the credentials I have? I've seen sp_addlinkedserver, but I don't think I need to use that because that database is on the same server? I just can't figure out how to 'switch' users to update the other database from the one stored procedure.

If I haven't made myself clear just let me know and I'll do my best to give a better explanation. Any help would be greatly appreciated.

Option 1:

Create a credential which has access on both db1 & db2 database. It will tune your query performance, bcs it is a direct access & you need not to stick with Ad-Hoc Remote quires.

Option 2:

Using Ad-Hoc remote quires using OPENROWSET or OPENDATASOURCE.

|||

As Mani indicates, the user will, in some way, require permission to access db2. However, it may not be necessary to provide the user direct access permissions. You may wish to explore the new 'EXECUTE AS' functionality (SQL 2005).

Refer to Books Online, Topic: "EXECUTE AS'

|||

Arnie,

I am not sure EXECUTE AS will help here.

As per my understanding, EXECUTE AS will affect the entire scope, if we need to change the context then we have to use the Execute AS & revert.

I created 2 users,

user1 only have access on db1

user2 only have access on db2

Without direct access permission on both db, user1 can't able to access the db2 rite? (even with execute as). If it allows then i can create any sp with Execute As target db user name, and i can access all the data.

Am i missing something here?

|||

Mani,

As in the Example , in Books Online, Topic: 'EXECUTE AS (Transact-SQL)', inside the stored procedure, the security context can be changed, the UPDATE statement can be executed in db2, and then the security context can revert -leaving the entirety of the context switching 'hidden' from the application users' view.

And, if necessary, the sproc can be encrypted locally to obfuscate the context switching from developers.