473,320 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Inter-database stored procedures and permissions

Hello all, this is my second post to this newsgroup. It's a question
about stored procedures and permissions and how these behave between
databases.

Here's the scenario. I have a database that stores information for a
system "A", and I have a different database on the same SQL server
that stores the login and other info "LOGIN". I write a stored
procedure in the "A" database that checks some tables in the "LOGIN"
database, let's call this "SP_A".

Additionally I have a user account that accesses all appropriate
stored procedures in "A" called "USER_A", and the same for the "LOGIN"
database, "USER_LOGIN".

Here's the part that raised my curiosity. I log into the server via
Query Analyzer using the "USER_A" account. I run "SP_A" which does a
join between some table in "A" and another table in "LOGIN". I give
"USER_A" execute permission on "SP_A", then I try to run "SP_A" and
get an error:

SELECT permission denied on object '(table in "LOGIN" database)',
database '(real name of "LOGIN")', owner 'dbo'

Huh? how come I need to assign additional select permissions in this
database if I'm not doing an actual select statement? I'm not even
dynamically running a select statement through an exec function. This
just struck me as odd, seeing as how I never explicitly set SELECT
permission on any table in "A" for "USER_A", yet my stored procedure
works, but between databases I have to assign extra permissions for a
stored procedure "SP_A" access to the tables in "LOGIN".

Anyone able to explain this behavior? Because I'm at a loss and I've
only been doing this DB thing for about 2 years.

Thanks in advance, all.
-TJ
Jul 20 '05 #1
4 7947
The same login must own both databases in order for the ownership chain to
be unbroken for the dbo-owned objects. Additionally, if you are running SQL
2000 SP3, you need to enable cross-database chaining the both databases.

The script below illustrates how you can implement cross-database chaining
security.

USE A
EXEC sp_changedbowner 'sa' -- or any common login
EXEC sp_dboption 'A', 'db chaining', true --if SQL 2000 SP3
EXEC sp_adduser 'USER_A'
--no object permissions are granted
GO

USE LOGIN
EXEC sp_changedbowner 'sa' -- or any common login
EXEC sp_dboption 'LOGIN', 'db chaining', true --if SQL 2000 SP3
EXEC sp_adduser 'USER_A'
GRANT EXEC ON dbo.SP_A TO USER_A
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP
"TJ Olaes" <ju**@olaes.net> wrote in message
news:fe**************************@posting.google.c om...
Hello all, this is my second post to this newsgroup. It's a question
about stored procedures and permissions and how these behave between
databases.

Here's the scenario. I have a database that stores information for a
system "A", and I have a different database on the same SQL server
that stores the login and other info "LOGIN". I write a stored
procedure in the "A" database that checks some tables in the "LOGIN"
database, let's call this "SP_A".

Additionally I have a user account that accesses all appropriate
stored procedures in "A" called "USER_A", and the same for the "LOGIN"
database, "USER_LOGIN".

Here's the part that raised my curiosity. I log into the server via
Query Analyzer using the "USER_A" account. I run "SP_A" which does a
join between some table in "A" and another table in "LOGIN". I give
"USER_A" execute permission on "SP_A", then I try to run "SP_A" and
get an error:

SELECT permission denied on object '(table in "LOGIN" database)',
database '(real name of "LOGIN")', owner 'dbo'

Huh? how come I need to assign additional select permissions in this
database if I'm not doing an actual select statement? I'm not even
dynamically running a select statement through an exec function. This
just struck me as odd, seeing as how I never explicitly set SELECT
permission on any table in "A" for "USER_A", yet my stored procedure
works, but between databases I have to assign extra permissions for a
stored procedure "SP_A" access to the tables in "LOGIN".

Anyone able to explain this behavior? Because I'm at a loss and I've
only been doing this DB thing for about 2 years.

Thanks in advance, all.
-TJ

Jul 20 '05 #2
Thank you for the prompt reply.

I checked the owners of all the tables, stored procedures, and the two
databases involved. All items seem to be under the ownership of "dbo", and
the owner of the two databases are the same, which is a windows account on
the system (COMPUTER\account). The dbo doesn't have a login on either
database, but I would think if the dbo of the two databases were the same
there should be no problem, right?

-TJ

"Dan Guzman" <da*******@nospam-earthlink.net> wrote in message
news:sJ*******************@newsread3.news.atl.eart hlink.net...
The same login must own both databases in order for the ownership chain to
be unbroken for the dbo-owned objects. Additionally, if you are running SQL 2000 SP3, you need to enable cross-database chaining the both databases.

The script below illustrates how you can implement cross-database chaining
security.

USE A
EXEC sp_changedbowner 'sa' -- or any common login
EXEC sp_dboption 'A', 'db chaining', true --if SQL 2000 SP3
EXEC sp_adduser 'USER_A'
--no object permissions are granted
GO

USE LOGIN
EXEC sp_changedbowner 'sa' -- or any common login
EXEC sp_dboption 'LOGIN', 'db chaining', true --if SQL 2000 SP3
EXEC sp_adduser 'USER_A'
GRANT EXEC ON dbo.SP_A TO USER_A
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP
"TJ Olaes" <ju**@olaes.net> wrote in message
news:fe**************************@posting.google.c om...
Hello all, this is my second post to this newsgroup. It's a question
about stored procedures and permissions and how these behave between
databases.

Here's the scenario. I have a database that stores information for a
system "A", and I have a different database on the same SQL server
that stores the login and other info "LOGIN". I write a stored
procedure in the "A" database that checks some tables in the "LOGIN"
database, let's call this "SP_A".

Additionally I have a user account that accesses all appropriate
stored procedures in "A" called "USER_A", and the same for the "LOGIN"
database, "USER_LOGIN".

Here's the part that raised my curiosity. I log into the server via
Query Analyzer using the "USER_A" account. I run "SP_A" which does a
join between some table in "A" and another table in "LOGIN". I give
"USER_A" execute permission on "SP_A", then I try to run "SP_A" and
get an error:

SELECT permission denied on object '(table in "LOGIN" database)',
database '(real name of "LOGIN")', owner 'dbo'

Huh? how come I need to assign additional select permissions in this
database if I'm not doing an actual select statement? I'm not even
dynamically running a select statement through an exec function. This
just struck me as odd, seeing as how I never explicitly set SELECT
permission on any table in "A" for "USER_A", yet my stored procedure
works, but between databases I have to assign extra permissions for a
stored procedure "SP_A" access to the tables in "LOGIN".

Anyone able to explain this behavior? Because I'm at a loss and I've
only been doing this DB thing for about 2 years.

Thanks in advance, all.
-TJ


Jul 20 '05 #3
"Thomas Joseph Olaes" <tj*******@ALLTHEshowfaxCAPITALLETTERS.com> wrote in
message news:1n*****************@dfw-read.news.verio.net...
Thank you for the prompt reply.

I checked the owners of all the tables, stored procedures, and the two
databases involved. All items seem to be under the ownership of "dbo", and
the owner of the two databases are the same, which is a windows account on
the system (COMPUTER\account). The dbo doesn't have a login on either
database, but I would think if the dbo of the two databases were the same
there should be no problem, right?

-TJ


Yes, the 'dbo' user ownership chain is unbroken if the database owners are
the same. The following should return 'COMPUTER\account' as the
owner/login. Don't forget that cross-database chaining needs to be enabled
too.
sp_helpdb 'A'
sp_helpdb 'LOGIN'
GO
use A
sp_helpuser 'dbo'
GO
use LOGIN
sp_helpuser 'dbo'
GO
--
Hope this helps.

Dan Guzman
SQL Server MVP
Jul 20 '05 #4

Dan Guzman wrote:
"Thomas Joseph Olaes" <tj*******@ALLTHEshowfaxCAPITALLETTERS.com> wrote in message news:1n*****************@dfw-read.news.verio.net...
Thank you for the prompt reply.

I checked the owners of all the tables, stored procedures, and the two databases involved. All items seem to be under the ownership of "dbo", and the owner of the two databases are the same, which is a windows account on the system (COMPUTER\account). The dbo doesn't have a login on either database, but I would think if the dbo of the two databases were the same there should be no problem, right?

-TJ
Yes, the 'dbo' user ownership chain is unbroken if the database

owners are the same. The following should return 'COMPUTER\account' as the
owner/login. Don't forget that cross-database chaining needs to be enabled too.
sp_helpdb 'A'
sp_helpdb 'LOGIN'
GO
use A
sp_helpuser 'dbo'
GO
use LOGIN
sp_helpuser 'dbo'
GO
--
Hope this helps.

Dan Guzman
SQL Server MVP


Solved my problem. Thanks.

Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: David M. Karr | last post by:
I've been asked to help debug a complex problem involving inter-frame references, so I just want to understand the elements involved with this. Apparently, there is a page with multiple frames,...
4
by: Frank Meng | last post by:
Hi. I am trying a csharp sample from http://www.codeproject.com/csharp/socketsincs.asp . (Sorry I didn't post all the source codes here, please get the codes from above link if you want to try)....
4
by: Viper Venom | last post by:
Dear All: I am trying to write an application that consist 2 executables 1) Server.exe 2) Client.exe I start the server.exe first and then start the two client exe by code and kill both of...
7
by: A.M | last post by:
Hi, What is the best way to implemet Inter Process Communication in .NET ? I developed two programs and I want to have them talk to each other. Thanks, Alan
13
by: Bern McCarty | last post by:
I have run an experiment to try to learn some things about floating point performance in managed C++. I am using Visual Studio 2003. I was hoping to get a feel for whether or not it would make...
6
by: les | last post by:
Here's a class which uses 2.0 generics to implement an inter-thread message queue in C#. Any number of threads can post and read from the queue simultaneously, and the message object can be any...
5
by: Joseph Geretz | last post by:
I need to communicate between two applications. The legacy application is in VB6. New development is in C#. Here's the scenario: The VB6 app will be pumping document files into a folder. We'll be...
0
by: Hugo Ferreira | last post by:
Hi everyone! Here's the current scenario: I have a program in Python that computes something very fast (<1s), but it takes a considerable amount of time to read the startup data (>90s). Since...
7
by: Sumedh | last post by:
Hi everyone There is a C# project which calls C++/CLI dll to be able to call native C++ including templates. But the C++/CLI code itself also requires the C# dll to get the types. For example: ...
0
by: dantz | last post by:
After reading all of the materials in msdn about interprocess communication now I am confused. I hope someone can give me some enlightment. I am developing a multithreaded client-server...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.