Post reply

Creating a view from a view

  • May 23, 2016 at 8:01 am

    #307984

    Hi

    I am looking to set up a view from a selection of other views and tables and grant access to a new login to only access this view.

    I have created the view and granted select access to the new user to the view, but when I log on with the new user it does not return any data. It doesn't error with permissions just no data returned. I can select data from this view when logged on with the creator account which is sysadmin.

    I have done the same but creating a view from a table and the new user can see the table okay. Is there an issue with the underlying tables that causes permission issues in this way? All the tables/views are created under the dbo schema.

    Thanks

  • May 23, 2016 at 8:36 am

    #1880784

    boughrogers (5/23/2016)

    Hi

    I am looking to set up a view from a selection of other views and tables and grant access to a new login to only access this view.

    I have created the view and granted select access to the new user to the view, but when I log on with the new user it does not return any data. It doesn't error with permissions just no data returned. I can select data from this view when logged on with the creator account which is sysadmin.

    I have done the same but creating a view from a table and the new user can see the table okay. Is there an issue with the underlying tables that causes permission issues in this way? All the tables/views are created under the dbo schema.

    Thanks

    This is a recipe for some of the worst performance you can imagine. I know that nesting views seems perfectly logical but it will absolutely destroy any chances of a well performing query. Take a look here where Grant goes into this and demonstrates a reasonable simple example. Even with something that simple the performance is shockingly hindered. https://www.simple-talk.com/sql/performance/the-seven-sins-against-tsql-performance/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 23, 2016 at 12:29 pm

    #1880864

    Hi Sean

    Thanks for the advice I wasn't sure if it would have any performance impact but I can see this would as you advise be a bad idea.I will look more to exporting the data as it is rather than generate a view from a selection if views.great link and made me think about how I set up my queries

    Thanks

    Ian

  • May 23, 2016 at 12:47 pm

    #1880871

    boughrogers (5/23/2016)

    Hi Sean

    Thanks for the advice I wasn't sure if it would have any performance impact but I can see this would as you advise be a bad idea.I will look more to exporting the data as it is rather than generate a view from a selection if views.great link and made me think about how I set up my queries

    Thanks

    Ian

    No reason you couldn't create a new view that is similar to another one of course. 😀

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply