March 19, 2007 at 12:23 am
I have a view in Database A, owned by DBO.
The view references a few tables in databases B and C. All tables in these databases are also owned by DBO. I've granted SELECT permission on the view to User A. When User A tries to run the view, he gets a permission denied error.
What's going on?
March 19, 2007 at 1:46 am
- did you enable crossdatabase ownership chaining ?
If not db1.dbo <> db2.dbo
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 19, 2007 at 9:55 am
If cross database ownership chaining is on (as ALZDBA has pointed out is needed unless you define specific permissions), do the users have access to both databases? Also, are the databases both owned by the same login?
K. Brian Kelley
@kbriankelley
March 19, 2007 at 5:05 pm
I don't have server-wide Cross DB Ownership enabled. I've since enabled it on the three databases in question (it wasn't enabled previously). Database A was owned by a windows domain user, and the other two by sa. I've detached and reattached Database A and it is now owned by sa as well. The User has public access to all three databases. I haven't restarted any services.
At this stage the user still can't run the view. Any ideas?
March 19, 2007 at 10:39 pm
In the future, sp_changedbowner can set the database owner without you having to detach/attach. Did you set the cross db ownership chaining on all the databases before or after the attach/detach? Based on what you've written, everything should be in place... same login owns the databases in question, objects are all owned by dbo, all databases are configured for cross database ownership chaining, user has the ability to access all the databases, and user has the appropriate rights on the view.
K. Brian Kelley
@kbriankelley
March 19, 2007 at 11:38 pm
Sorry for the complexity, but here's what I did:
1. Set chaining on all three databases before the reattach
2. Reattached the db as sa
3. Checked chaining on all three dbs and found the newly-attached db was missing chaining, so I added it. The other two were OK still
I've looked at the sysxlogins table and all the users are unique; that is, I can't see any usernames which are duplicated but have different IDs (not sure if this would ever be the case anyway, but thought I'd check).
User has select rights on the view, but permission is being denied on one of the tables in Database B (via SELECT * FROM view).
March 20, 2007 at 12:16 am
I know you've re-attached as sa, but check the ownership of all three database again by using sp_helpdb '<database>'
If any of them aren't owned by sa, switch to that database and force the ownership change:
USE <database>
GO
EXEC sp_changedbowner 'sa'
GO
Then verify the user has access in all 3 databases. Query sysxlogins for the SID. Then, for each database, query sysusers for the individual databases, making sure an entry is present with the SID that is specified in sysxlogins.
K. Brian Kelley
@kbriankelley
March 20, 2007 at 4:11 pm
Interesting...
Running sp_helpdb for each database shows the owner is sa for each DB.
The sysxlogins table shows sa with sid 0x01. The dbo users in both Database B and Database C have SID 0x01. However, the DBO user in Database A (with the view) has an entirely different, much longer SID. Ownership chaining is definitely turned on.
What now?
March 20, 2007 at 4:34 pm
USE DatabaseA
GO
EXEC sp_changedbowner 'sa'
GO
K. Brian Kelley
@kbriankelley
March 20, 2007 at 4:39 pm
Woohoo! That fixed it.
Thanks very much for your help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply