Cross DB Select from SP
-
OK - This must be simple - but I am confused. I have Database "GlobalInfo" with a table taPerson - owned by giOwner. I have set u a role called GIAdmin - which has a member called perAdmin.
In the database I have a stored procedure paPersonAdd to insert new entries. Rights are granted as...GRANT EXECUTE ON paPersonAdd TO GIAdmin.
When I log into the system as perAdmin, there are no errors executing paPersonAdd. So far, so good.
I have a sibling database 'LocalInfo' also owned by giOwner. It also has a role of GIAdmin with a member perAdmin. In this DB I have a proc called paLogin with rights granted to GIAdmin. Inside this routine, I execute the statement "SELECT pwd FROM GlobalInfo.dbo.taPerson WHERE LoginCode = @login".
When I attempt to run the procedure as perAdmin, I get a SELECT permission denied on object 'taPerson'. I thought granting rights to the procedure was enough.
Please set me straight.
Guarddata-
-
Is Cross DB Ownership Chaining setting no both database be turned on? If not, enable it and try again.
-
Ahh - it was so simple
- ThanksGuarddata-
-
Cross Database ownershp chaining works, but you have to remember that enabling that means it works all the time. SO if you have tables you do not want to have accessed it is a better security practice to leave cross database ownership chaining off (which is why it is off by default) and grant priveleges on the user(s) who need access. It is more work, but it is more secure.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Jack - I agree with you completely. I actually changed my routines to just have a view with rights so I don't open up everything. Thanks for making the point clear.
Guarddata
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply