June 30, 2003 at 5:24 pm
In a statment such as
Select t1.columna, t2.columna From Db1.dbo.t1 Inner Join Db2.dbo.t2
found in a miscellaneous sroc say, Stp_Test1
users with only rights to the sproc should be allowed to run it right? for some strange reason the select permissions for a particular table are not given. I'm not really sure why... they are denied as seen in this error message
Server: Msg 229, Level 14, State 5, Procedure STP_xxxxx, Line 33
SELECT permission denied on object 'Tblxxx', database 'xxx', owner 'dbo'.
help?, Thanks
-Francisco
Edited by - fhtapia on 06/30/2003 5:24:51 PM
-Francisco
July 1, 2003 at 3:59 am
You have to have cross db ownership chaining enabled and the user has to exist in the other db as well. SP3 would have broken this if you didn't check the chaining checkbox during the install.
Andy
July 1, 2003 at 9:04 am
My setup is Sql Server 2000 w/ SP2, and I see the checkbox for cross db ownership chaining but it's grayed out.
I've checked that the user exists in both db's, but do I have to give him select access to the table in the 2nd DB (DB2) in order to allow the sproc to join to it?
Thanks,
-Francisco
-Francisco
July 2, 2003 at 4:12 am
Not if all objects are owned by the same owner.
Andy
July 2, 2003 at 10:03 am
I tend to create a view in the first database to access the second. Helps if I need to move things by abstracting. Also doesn't tie my code to some database name (other than the view).
Same security items brought up above still apply.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 2, 2003 at 3:14 pm
I was hoping to avoid refering a view, but in any case wouldn't the view suffer the same problem since it's accessing objects from the other database?
... just tried it and I still get this error:
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Tblxxx', database 'xxx', owner 'dbo'.
what gives? all objects are owned by dbo I've double checked w/ sp_helpdb and sp_help and all return w/ the owner name as "dbo".
I don't know why but would really like to know....
btw this server is still sp2 so there is no db owner chaining option as of yet I've put in for an upgrade request, I found out that the reason I was able to see the option greyed out was because my local client pc has SP3 loaded.
Thanks again for your time into this.
-Francisco
-Francisco
July 2, 2003 at 3:29 pm
Prior to SP3, Cross-database ownership chaining is on, but you're right, the option will be greyed out in EM because it's smart enough to realize that SP2 doesn't support changing the setting.
Does the user have a user account in the database? The user must for cross-database chaining to work. Even if the guest account is enabled, that's sufficient (hence the reason you have to have guest enabled for master and tempdb). If the user does not have access, you'll receive the error you've cited.
With that said, if the user doesn't exist, I'd recommend setting up a user/role structure similar to what you have in the first database. The use of the guest account is generally looked at as an option when everything else has failed.
Be aware, the user doesn't have to any rights to the tables or views in question. Simply being a user in the database is sufficient should the ownership chain be in place.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
July 2, 2003 at 5:05 pm
quote:
With that said, if the user doesn't exist, I'd recommend setting up a user/role structure similar to what you have in the first database. The use of the guest account is generally looked at as an option when everything else has failed.
I do have my user added to both databases. In Database 1 they have access to execute a handful of procedures, in database 2 they have not been granted "any" rights they are just added in so that they belong on that database. I really didn't want to grant them table level access because I thought that the procedure (which joins tables from Database1 to tables in Database2) would take care of retreiving all that info.
-Francisco
-Francisco
July 2, 2003 at 5:06 pm
quote:
Be aware, the user doesn't have to any rights to the tables or views in question. Simply being a user in the database is sufficient should the ownership chain be in place.
I didn't read this line well the first time... how can I make sure that the ownership chain is in place when in SP2?
-Francisco
-Francisco
July 2, 2003 at 6:05 pm
Owner of both objects match the same login at the SQL Server level.
Are the databases owned by the same login?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
July 3, 2003 at 9:15 am
when I right click properties on db2 I get Owner "Sa"
when I right click properties on db1 I get Owner "Sa"
is there somewhere else I should check?
-Francisco
-Francisco
July 3, 2003 at 9:24 am
It sounds like you have the i's dotted and t's crossed. What happens when you execute the stored procedure as a member of the sysadmin role?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
July 3, 2003 at 10:18 am
as My myself or "sa" the procedure executes w/o fail. When I log in as my User who only has access to the procedure in DB1 it fails w/ errors that they don't have SELECT rights on the joined table names owned by "dbo".
:(, I have put my request to upgrade the server from SP2 to SP3 hoping this will help but so far I have not received word from the IS dept.
-Francisco
-Francisco
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply