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 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
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
"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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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,...
|
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)....
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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:
...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |