Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Users Creating queries in sql

    Hi all I have an Database that has Access 2007 as the front end and SqL 2005 as the engine. I am wondering how can I make it so that users can safely create queries/views without touching the tables or seeign them??

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by desireemm View Post
    I am wondering how can I make it so that users can safely create queries/views without touching the tables or seeign them??
    this sounds like a recipe for disaster

    how can anybody be expected to write a query without seeing the tables?

    even then, i would hardly ever let users write their own queries without ~extensive~ training, not just in the tool (fortunately msaccess has a really nice interface, the query builder is superb), but also in the data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Well I wish my boss felt the same way you do, cause they want me to give a user access to make queries and I dont trust this person at all. They worked on Access (badly I might add) and now they think they know SQL. Such a nightmare

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    I disagree

    I have got to believe that, with the proper use of a few preformatted views of the database's data, and with appropriate restrictions on user security, that the smart people on this site could come up with some scheme that would allow reasonable data access to users.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, ken, we were all sorta waiting for you to start...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking

    I agree with R937

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I can create some views for them to work with, I just dont want them to have access to the tables. I only want them to work with the views only and create queries out of those

  9. #9
    Join Date
    Jan 2011
    Posts
    1
    we get round the trust issue by restricting user access to read only on specific objects. We then get people to load Management Studio and querying the DB from there. If there is a confidentiality issue you will need to create views and set access appropriately. Advantage of this is that the user becomes familiar with the environment without breaking things. Just keep an eye on performance, in case someone writes absurd queries.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create a schema called "adhoc" to store views for data access. Create a role that has read-only access to the objects in the adhoc schema. Assign your users to the role.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Dec 2010
    Posts
    1
    Sounds like you only have to allow this one user the ability to create queries.

    I say give this person read only permissions on the tables/views. They won't be able to modify the database's design so the worst thing they could do (unless I've forgotten something) is create a badly performing query. Which is not good thing to have, but you could work with them to improve it.

    Shouldn't be any permanent damage to the database and your boss and this person will be happy.

  12. #12
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Quote Originally Posted by desireemm View Post
    Well I wish my boss felt the same way you do, cause they want me to give a user access to make queries and I dont trust this person at all. They worked on Access (badly I might add) and now they think they know SQL. Such a nightmare
    How many year you been doing it then tell him that how long training we be

    Or

    Take a backup let the boss hang him else. Lol

    Not a good move but I have 1 customer who thinks like that all I end up doing is unbreaking things at a bloodily good hourly rate. Tho

    Eg one day get a phone '"I have deleted all data from a table" but you can't recover from a backup as other had been doing data entry do you have a plan
    After some thing
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    the user has read only access to some queries that I created, she cant see the tables. is that ok?? I dont want her to have access to tables

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It is Ok by me. If it meets all of your needs, you're good to go.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I dont see why she has to create queries, cant she just read only

Posting Permissions

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