By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,714 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,714 IT Pros & Developers. It's quick & easy.

Inter-database stored procedures and permissions

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
"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

P: n/a

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 discussion thread is closed

Replies have been disabled for this discussion.