Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert Views to T-SQL (SQL Server 2K/2K5)

    Since there's no SQL Server room here...

    Our IT guys are deligently trying to enforce the rule that no tables shall be accessed by any end-user and that all db objects be wrapped in SQL Server roles and permissions. To that end, they require all data access to go thru T-SQL instead of Views (never, of course, shall a table be directly read)... In my shop, we anticipate doing a fair amount of testing on data in SQL Server and want to be able to go all the way to the end of the line: write, delete table contents, create, drop tables, etc. while developing a structure. My question here is, is there a wizard or utility or something that can allow me to quickly and painlessly create a T-SQL proc to replace any views ultimately developed in testing? This should meet the IT dept's security concerns when it comes time to deploy into production.

    I have written some amount of T-SQL but am by no means an 'expert'. I am more familiar with Views and, even more so, familiar with accessing SQL Server via Access/ODBC. So, any help towards creating secure roled/permitted T-SQL given my level of expertise would be appreciated. If there's no good canned solution any further advice would be great.

    TIA

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert Views to T-SQL (SQL Server 2K/2K5)

    Hi Steve

    I have responsibility for a number of servers that have databases on them, one server is my reporting server and is updated using a web service. I have a reporting application that reads from this database and is very slow. I have a user who is reasonably experienced in T-SQL and sometimes wants to have a look at the raw data as he does multivue work on my system.

    To allow him access to this data I have given him read only access to the database using Windows Authentication, he is allowed to rattle away at the data to his hearts content without fear of deleting or changing data. All you would need is Enterprise Manager (EM) which are part of the SQL set up disc but you may have to ask the IT department if there are sufficient licences ( tell them you only want the tools, not the full installation).

    At least with EM you can use the basic query grid and/or analysis services..HTH, my starter for 10
    Jerry

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Views to T-SQL (SQL Server 2K/2K5)

    thanks for the response. my shop has EM and all that - most of us are "former" IT people. The issue appears to be that, in order to satisfy security concerns, even a read-only access to a table in a db is not encouraged (be it via ODBC or EM). At this point in time, I am getting resistence in requesting dbo-level access to a copy of a database sitting on a server currently protected from access via Windows Authentication/network. (That is, only people in our group can even map to the drive.) I must have development control over SQL Server databases as the work we do often requires extensive data manipulation beyond Access's abilities. But, one step at a time... if I can guarantee 'production' dbs with T-SQL instead of Views that would be a win. Hence the question re. converting Views to T-SQL.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Views to T-SQL (SQL Server 2K/2K5)

    Hi Steve,
    I'm not sure where you are accessing your data from (i.e. Access front-end, ASP pages, etc...), and probably don't understand your question entirely, but you can always "script" out your views, copy and paste the generated code into Query Analyzer (SQL 2000) or into the Query Editor Window of SS Management Studio (SQL 2005). Right clicking on the view's name, with either of these 2 utilities, will get you to the scripting menu.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •