Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    38

    Unanswered: "Changed Context to ..." message when editing permissions w/ VBA

    I have a client using a small SQL Express database with an Access front end.

    I've created a form in the front end that allows a supervisor the ability to perform two very basic security operations. 1) Adding a new user, and 2) granting/removing write privileges. The code behind the form uses VBA to build some T-SQL strings and then executes them via an ADO connection.

    The strings are as follows:

    1) Add new login to server:
    "CREATE LOGIN [NETWORK\UserName] FROM WINDOWS WITH DEFAULT_DATABASE = MyDatabase"

    2) Add user to database:
    "USE MyDatabase CREATE USER [NETWORK\UserName]"

    3) Grant Write Permissions:
    " USE MyDatabase EXEC sp_addrolemember 'db_datawriter', 'NETWORK\UserName' "

    4) Remove Write Permissions:
    " USE MyDatabase EXEC sp_droprolemember 'db_datawriter', 'NETWORK\UserName' "

    I have full privileges over the server & database, and everything runs perfectly.

    However, when the supervisor - whose login includes security admin privileges - tries this, he gets a message that says "Changed Context to MyDatabase, Error# -2147217900, 80040e14" and the code fails.

    If I grant him "db_owner" status, the message does not come up and everything runs perfectly.

    I'm not a security guru or anything, but I suspect that it's not the greatest idea to hand out db_owner privileges.

    I ran a search, and the best I could come up with were a couple of KB articles:
    http://support.microsoft.com/default...B;EN-US;197459
    http://support.microsoft.com/default...B;EN-US;143339

    I'm not even sure these articles are talking about what's happening..

    Any ideas?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    When is the last time your boss ran an office update? Or more specifically, which MDAC is he using?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2004
    Posts
    38
    Quote Originally Posted by Teddy
    When is the last time your boss ran an office update? Or more specifically, which MDAC is he using?
    I'm not at the client's location right now, so I can't give a firm answer.. but I had someone there gather the following info..

    Access is version 11.6566.8132 SP2

    They're running XP Pro version 2002 SP2.

    Per this page: http://support.microsoft.com/kb/231943/en-us they should be on at least MDAC 2.8 SP1. However based on the release dates shown on that page, I wouldn't be suprised if they were running 2.8 SP2.

    Does that help?

  4. #4
    Join Date
    Oct 2004
    Posts
    38
    Ok, went by their office yesterday. MDAC is version 2.8.1117.0.

    After a little more time spent, it appears as if this is the 'informational' message passed back to ODBC. It appears that I need to figure out how to trap for it in VB. The KB articles I posted got me started, but I need some more work..

    I also seem to still have a SQL issue.. In the little bit of troubleshooting I had time for, it also appears that I have a 2nd error coming in behind the "changed context.." message. If the user is not a dbowner, it says that he doesn't have permissions to run these operations.

    Is this correct? Is dbowner status required to change permissions of other users?

  5. #5
    Join Date
    Oct 2004
    Posts
    38
    Sorry to resurrect an old thread, but I was wondering if there were any further ideas on this?

Posting Permissions

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