T-SQL

[ventalinks@nospammers.com]

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

Re: T-SQL

On Thu, 02 Oct 2008 10:44:09 -0400, ventalinks@nosp ammers.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@nospammers.com]

Re: T-SQL

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]

Re: T-SQL

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.T ableA AS A
JOIN Database2.dbo.T ableB 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@nospammers.com]

Re: T-SQL

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.T ableA AS A
JOIN Database2.dbo.T ableB 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]

Re: T-SQL

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.T ableA AS A
JOIN LinkedServer.Da tabase2.dbo.Tab leB AS B
ON A.keycolumn = B.keycolumn;

More details on linked servers and
sp_addlinkedser ver/sp_addlinkedsrv login 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/library/ms191296(SQL.90).aspx

http://articles.techrepublic.com.com/5100-10878_11-6158511.html

--
Plamen Ratchev

http://www.SQLStudio.com

[ventalinks@nospammers.com]

Re: T-SQL

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]

Re: T-SQL

(ventalinks@nos pammers.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.T ableA AS A
>JOIN Database2.dbo.T ableB 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 malconfiguratio n. 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@sommarsk og.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