SQL Server 2005 permissions problem - makes no sense

[katgirl]

Hi,

This is related to my other thread. I have worked through the problem some more. I granted connect to guest in the database that has the tables referenced by the view.

Now, I am getting error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object

Which makes NO sense to me, because the user has the db_datareader/writer roles assigned to it in the database... so it can select from any table in the database.

So why why why am I a still having this problem???

 

[mrdenny]

It would probably be easier if you enable database chaining between the two databases. Then the permissions would come across automatically.

Be sure to leave the guest account in the second database so that the user can log into the database.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katgirl]

Hi mrdenny,

Here's what I read about chaining this morning:

Assuming all objects involved have the same owner, you can instead grant no permissions to guest and enable cross
database chaining in both databases so that database A users can access database B data only through your database A view. The 'db chaining' database option must be turned on in both databases in order for the cross-database ownership chains to be honored.

I am little concerned about doing this... the above seems to imply that if I set up chaining then users in database A can ONLY see the data in database B through the view... not sure what the implications of doing this are, or does it not matter... since it only applies to this particular view??

What if I have other views that also access data across databases? Will the users be restricted due to the chaining on the other view? Or maybe I am just not understanding this at all.

Anyhow, I fixed it the easy way - by granting read only permissions to guest on all the tables in the view. Not the greatest solution, though...

 

[mrdenny]

Cross database chaining works by allowing any view or procedure to access objects in the second database. If new views or procedures are created in the first database those would be able to see there child objects in the remote database.

Cross database chaining is the best option as it allows then to see the data without having to give them rights to the data.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com