"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:sJ3Ib.14731$IM3.12960@newsread3.news.atl.earthlink.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.com...
> > 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
>
>

"TJ Olaes" <junk@olaes.net> wrote in message
news:feab9d89.0312291525.4f1893a3@posting.google.com...
> 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 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