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.
-TJThe 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" <junk@.olaes.net> wrote in message
news:feab9d89.0312291525.4f1893a3@.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|||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" <danguzman@.nospam-earthlink.net> wrote in message
news:sJ3Ib.14731$IM3.12960@.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" <junk@.olaes.net> wrote in message
> news:feab9d89.0312291525.4f1893a3@.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|||"Thomas Joseph Olaes" <tjTAKEOUT@.ALLTHEshowfaxCAPITALLETTERS.com> wrote in
message news:1n4Ib.4695$5M.99324@.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|||Dan Guzman wrote:
> "Thomas Joseph Olaes" <tjTAKEOUT@.ALLTHEshowfaxCAPITALLETTERS.com>
wrote in
> message news:1n4Ib.4695$5M.99324@.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.