Can you change users within a stored procedure?

[forecasting]

I have two databases in the same instance of SQL Server, DB1 and DB2. I connect to each database with a separate login that is not shared between databases (login1 for DB1 and login2 for DB2). When running a particular stored procedure on DB2 I want to insert a record on DB1. I can do this with a linked server, but I want to know if it is possible to by changing my connection string. For example,

select name from DB1..Users where (unique = 1)
(CHANGE connection)
insert into DB2..UserID (IDname) values (name)

 

[Qik3Coder]

if you back out of the database and deal with it at the UI code level, so that you have two separate transactions.

Q:Are the databases on the same server and the user just doesn't have rights to it, or are they on two separate servers?

-Sometimes the answer to your question is the hack that worksi]

 

[SQLDenis]

grant insert permission on that table to the other login

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[forecasting]

the databases are on the same server and the user just doesn't have rights to the other database.

Since it appears from your answers that you can't change the connection string within a stored procedure, it appears y best best is to 'grant insert permission on that table'.

Thanks for your help!

 

[mrdenny]

The only way to change users is with the EXECUTE AS command. However that changes users, not logins. As users are database specific this won't help you out very much. Your best bet will be to grant the login rights to the second database. Then turn on database chaining on both of the databases. This will allow the user to run the procedure without having to grant any specific rights to the table in the second database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog