Hello,

I need to write a stored procedure that has two different user
connections to two different databases. Both db's are on the same one
server. Is this possible to create using Transact-SQL commands in a
stored procedure?

Thanks :-)

On Thu, 02 Oct 2008 10:44:09 -0400, ventalinks@nospammers.com wrote:

>I need to write a stored procedure that has two different user
>connections to two different databases. Both db's are on the same one
>server. Is this possible to create using Transact-SQL commands in a
>stored procedure?

A stored procedure runs on one connection, but it can reference
multiple databases on the same instance of SQL Server. If you
describe what you actually need to do someone may be able to suggest
an approach.

Roy Harvey
Beacon Falls, CT

Roy Harvey (SQL Server MVP) wrote:
> A stored procedure runs on one connection, but it can reference
> multiple databases on the same instance of SQL Server. If you
> describe what you actually need to do someone may be able to suggest
> an approach.

Thans for your reply. I wished to creat a report. Some data of the
report is confidential data. It is in another database requiring
different username and password. I am given all necessary usernames and
passwords. I would want to write the report that will combine these
data and regular data. Is this possibility?



> Roy Harvey
> Beacon Falls, CT

Since both databases are on the same server, you can reference both in a
single query (assuming user has permissions to both, etc.):

SELECT <columns>
FROM Database1.dbo.TableA AS A
JOIN Database2.dbo.TableB AS B
ON A.keycolumn = B.keycolumn;

Note that each table is prefixed with database and schema name.

--
Plamen Ratchev
http://www.SQLStudio.com

Plamen Ratchev wrote:
> Since both databases are on the same server, you can reference both in a
> single query (assuming user has permissions to both, etc.):
>
> SELECT <columns>
> FROM Database1.dbo.TableA AS A
> JOIN Database2.dbo.TableB AS B
> ON A.keycolumn = B.keycolumn;
>
> Note that each table is prefixed with database and schema name.


Hello,

There are two different usernames and one has not access to the other
database. I am getting this error. I think this is do to access
privilige limitation:


The server principal "x" is not able to access the database "y" under
the current security context.

One way would be to create a linked server to the confidential database
using the user/pwd provided, then in a query you can reference the
linked server:

SELECT <columns>
FROM Database1.dbo.TableA AS A
JOIN LinkedServer.Database2.dbo.TableB AS B
ON A.keycolumn = B.keycolumn;

More details on linked servers and
sp_addlinkedserver/sp_addlinkedsrvlogin to create linked server and
login mapping:
http://msdn.microsoft.com/en-us/library/ms188279.aspx
http://msdn.microsoft.com/en-us/library/ms190479.aspx
http://msdn.microsoft.com/en-us/library/ms189811.aspx

Also, you can use context switching to gain access to the confidential
table via a different user:
http://msdn.microsoft.com/en-us/libr...6(SQL.90).aspx
http://articles.techrepublic.com.com...1-6158511.html


--
Plamen Ratchev
http://www.SQLStudio.com

Plamen Ratchev wrote:
> One way would be to create a linked server to the confidential database
> using the user/pwd provided, then in a query you can reference the
> linked server:


Thanks for all of those links. I will research them and see if they
in my case.

(ventalinks@nospammers.com) writes:
> Plamen Ratchev wrote:
>> Since both databases are on the same server, you can reference both in a
>> single query (assuming user has permissions to both, etc.):
>>
>> SELECT <columns>
>> FROM Database1.dbo.TableA AS A
>> JOIN Database2.dbo.TableB AS B
>> ON A.keycolumn = B.keycolumn;
>>
>> Note that each table is prefixed with database and schema name.
>
>
> Hello,
>
> There are two different usernames and one has not access to the other
> database. I am getting this error. I think this is do to access
> privilige limitation:
>
>
> The server principal "x" is not able to access the database "y" under
> the current security context.

Before you do anything else, you should speak with your DBA. Maybe this
is just a case of malconfiguration. May the login x should be a user in
y as well. In that case, what Plamen suggested will work.

If the configuration is correct, you will need to set up a few things.
Or maybe rather someone with the powers-to-be will have to. There is a
longer article on my web site that discusses permissions in general,
including cross-database access: http://www.sommarskog.se/grantperm.html
that should get you started.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx