T-SQL

[ventalinks]

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 :-)

[Roy Harvey (SQL Server MVP)]

On Thu, 02 Oct 2008 10:44:09 -0400, ve********@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

[ventalinks]

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

[Plamen Ratchev]

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

[ventalinks]

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.

[Plamen Ratchev]

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

[ventalinks]

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
help in my case.

[Erland Sommarskog]

(ve********@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, es****@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