Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Adapter Configuration Wizard & SQL Server SPs (VB.Net 2002 & SQL Server 2000)

    Hi, I wonder if anyone can shed any light on my problem:

    Hi have two accounts for the SQL server, a SA account and a User account. The user account has exec permissions only on the relevant stored procedures to allow select, insert, update and delete records on the underyling tables.

    When I run the storedprocedures, under the user account from Query Analyser, they execute normally and work fine.

    On my VB.NET form, I have a SQL data Adapter - which I go to configure with the DA Configuration Wizard. I use the 'user' login in the connection with the wizard, I select the appropriate select, update and insert stored procedures from the combos and the make sure the mappings are all OK. Then when the wizard goes to configure the DA, it suceeds on the SELECT, but fails on the insert and update stored procedures, return the message that it could not find the stored procedures in the database!

    When I run the DA Configuration wizard with a connection using a SA account, it all works fine.

    This to me seems like a permissions problem, however, I have confirmed that the permissions are all OK, by running the stored procedures from query analyser as the User account.

    Anyone have any ideas (PS: I suspect that the same problem that is causing this is causing the application some problems, which is why I wish to resolve the cause of this).

    Cheers

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Data Adapter Configuration Wizard & SQL Server SPs (VB.Net 2002 & SQL

    Does SQL Server keep a log that will show you whether the user account credentials were passed properly by the control at runtime?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Adapter Configuration Wizard & SQL Server SPs (VB.Net 2002 & SQL

    Not that I can think of - but I'm not an expert.

    For interests sake, I have discovered if you make the account a System Admin, then the wizard functions fine. The wizard is likely using some stored procedure or something somewhere in a system database i would think...don't know what for though, or why it wouldn't need to use said stored procedure for a SELECT but would for an UPDATE, INSERT or DELETE?

    I will just give that user 'Sys Admin' rights while I am developing, although not 'best practice' it will save me a lot of headaches, I will then put them back to a standard user once I have finished development.

    Cheers

Posting Permissions

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