October 24, 2007 at 9:47 pm
Comments posted to this topic are about the item Views for Abstraction
October 25, 2007 at 1:08 am
Hi Andy,
thanks for this nice article.
Just as additional information, a link to BOL which explains when views are updatable and when not.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/aecc2f73-2ab5-4db9-b1e6-2f9e3c601fb9.htm
Best Regards,
Chris Büttner
October 25, 2007 at 7:02 am
Hi Andy,
With reference to your example of using tableA and tableB and a view to do a mass truncate and insert whilst keeping the data available for access. Would snapshot isolation which is available from SQL 2005 not be a neater alternative?
Kind regards,
Will - BuR
October 25, 2007 at 7:26 am
That's interesting. A case of using the tools I know more than the tools I don't know as well. I'd want to give it a try to see how it held up, we might be talking in some cases a 100 million rows or more, and copying all that to the version store might get pricey, but it does have some potential elegance to it!
October 25, 2007 at 7:41 am
I'm dealing with a lot of cross-server queries. My answer was to make the data access layer in the apps deal with it. Hard-coded SQL just isn't good. Synonyms in 2005 would work, but there's just too many different tables to make it manageable. I'd hoped to remove the owner complexity of JDE tables by creating a synonym for server.jde.proddta (say, as JDE) and then referencing tables as JDE.f0101, but it won't let me cheat that way. :angry:
October 25, 2007 at 8:15 am
Cool article. Gave me an idea for my data archive procedures. I can create a TransactionHistory table with the year appended to the name. The TransactionHistory view can UNION ALL. I could then migrate the older years info to another sever.
ATBCharles Kincaid
October 26, 2007 at 5:23 am
Nice article. Very thought-provoking. I do have a concern. Back in the days of SQL 6.5, views were a big performace issue (then again most things in 6.5 were a performance concern). I've done very little with them over the years since. May be time to revisit them since abstraction is the norm in our world.
October 26, 2007 at 5:39 am
Im still cautious about piling views on top of views, though in practice it seems to work more often than not. With performance, it always depends!
November 1, 2007 at 8:14 am
Good article.
I have a case where I need to pull data from another DB and created view on the same DB as the SP but I get an error "The SELECT permission was denied on the object 'abc', database 'bcd', schema, 'cde'." The user has select permission to the view but not the base table in the other DB. Anyway around this that I'm not seeing? I really didn't want to give select access to the base table but I did grant the public role in the other DB.
Thanks
Carl
November 1, 2007 at 8:44 am
I don't know if this applies, but I thought I would throw it out there anyway. If you are using Dynamic SQL to generate the select statement then you may want to read these articles.
http://www.sommarskog.se/dynamic_sql.html#EXEC4
http://www.sommarskog.se/grantperm.html#EXECASstatement
With Dynamic SQL and using the EXEC() statement, the caller must have explicit SELECT rights on the table (view) being reference. We've gotten around this (at least for the moment) by adding "WITH EXEC AS OWNER" to the SP and granting execute rights to the SP specifically to a select group. We are then able to insulate the table from the users directly.
Hope this helps.
November 1, 2007 at 8:51 am
Thanks for the post. The sql is not dynamic. Here is the code in basic terms...
the view: select * from dbB.dbo.tblB
the sp: select * from dbo.tblA join dbo.viewB on....
verry basic stuff and it seems that if the user has select rights on the view that those rights should flow to base objects as well. Having to grant select rights on all the base objects is a pain.
Thanks
November 2, 2007 at 9:14 am
Carl, have you tried enabling cross database ownership chains?
November 6, 2007 at 1:48 pm
I did not but I now have a better understanding of it... different object owners so it didn't seem to work. I didn't set it up before because I didn't know the security holes it would open. In our case they seemed acceptable but it ended up not working so it is off again.
Thanks for your recommendation. I ended just doing the extra security.
Carl
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply