Problem with dbo in Access 2000 project

[cmmrfrds]

In using Access 2000, I created all my stored procedures and tables under the login of "sa". When I try a different login name the tables appear with dbo in the table list and the Forms run okay, but where I used a stored procedure as the data source for a Form I get an error that the stored procedure cannot be found. When I edit the Form it shows the stored procedure with a dbo. in front of the stored procedure. If I use the stored procedure with the dbo. it runs okay. The problem is that I don't want to change the Forms for each new user.

How do I get rid of the dbo? I tried using a "role" with execute access to the stored procedure and assigned my login to the role, but it didn't make any difference. Do I need to switch my login name once I am connected? If so, how is it done?

Thank you,
Jerry

 

[TomSark]

No... you should ALWAYS deploy all of your database objects in production as being owned by dbo to avoid broken ownership chains...

Have you granted the EXECUTE permission on the proc to the accounts and/or Roles that need to access it? I believe this is your problem...
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[cmmrfrds]

Tom, are you using Access 2000?

Yes on the execute permissions. I made a role and assigned execute permission to the stored procedure. Then, I made my login a member of the role with execute permissions. I am quite confident that is not the problem.

The problem is that when logging in as "sa", owner, Access 2000 returns the table and stored procedure names without the dbo. prefix, which get attached to Forms. When logging in with any other login name, Access 2000 returns the names prefixed with dbo.. The work around appears to be when developing use the dbo. prefix for all the tables and stored procedures. Is there a better way?

Jerry