Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: EXECUTE AS in MSSQL2000

    Hey Guys and Gals,

    here's an interesting one for you.

    Ok, so this 3rd party application we have exhibits some interesting behavior; when you amend any table through it's interface it drops and re-adds the object; meaning all permissions to the object are also removed. (Just take my word for it when I say that the table changes have to be done through this app).

    Now, I've built a little application that hooks into this using an SQL Server authenticated user account with SELECT only permission on particular tables. Currently the app uses sprocs to access data. However, when changes were made to the schema last week; the application, obviously, received permission errors and ground to a nice halt.

    I know that in 2005 we have the lovely EXECUTE AS statement; but I'm running a 65compaability database here and don't have that functionality.

    Any ideas on how I can sort this mess out?

    Hope I explained this well enough, let me know if you need any more info

    Cheers,
    George
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Read only access to entire database (perhaps in conjunction with DENY to unwanted objects... which are no doubt dropped and recreated too)?
    List of tables the login requires access to and some process that assigns these permissions if ever they disappear?

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    They only have SELECT permission on a small number of the tables e.g.employee details are visible, but pay information is not!

    And yes, unfortunately there is a possibility for every object to be dropped and readded within this schema; some far less frequently than others.

    You might be on to something though; I could probably write a sproc to grant the necessary permissions which can be run on application load...

    Nice one Pootiful
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol - you are in a rum mood.

    Quite a little headache this app of your int it?

    One problem with running this on application load - your application will need to run it with an account with sufficient permissions to grant read permissions....

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yep, I just came back on to point that out.

    Tbh I think I'll just write a proc that I will execute when we have schema changes. Sod trying to automate it, I suppose it doesn't happen all that often.

    All this because I had to change the precision of a decimal field from 5,1 to 6,2!
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    When are you going to start trialling compatibility 90 eh?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I use that too... Just tend to spend more time (and hit more problems) using this 65 compat.

    Got in this morning and was asked how my course was last week...
    Quote Originally Posted by manager
    right, well we better find you something to practice on
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Best thing to hear that. 84.3% of everything you learn in training will be forgotten in 5 weeks if you don't use it. The remaining 12.4% takes only another 4 days.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    84.3% ... The remaining 12.4%
    What about the bit inbetween?
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    George, your maths is quite shocki....

    Oh yeah. That um... you lose during the lunch breaks

Posting Permissions

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