"dbo." problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

"dbo." problem

Author

Topic

Moussie
Starting Member

20 Posts

Posted - 2002-08-01 : 04:32:11

Hello!

I am working with securing an old, not secured app.
It runs as an Access ADP-file against a backend
SQL-2K server. Currently, everybody is logging in
as sa (yay...).

Now, because you are logged in as sa, Access will
happily use "SP_MYPROC" as a source for a report,form
or combobox. But when you log in as a restriced user
it complains that it can not find sp_myproc, and it is
not happy until you tell it "dbo.sp_myproc".
There is no way I can wade through and change 100s of
forms and recordsources and add "dbo." in front of
them so I wondered if there is another way, except
of letting all the users to become members of sysadmin.
That, is obviously not an option. :)

Regards,
Moussie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-01 : 07:39:59

Don't make them sysadmin members. You *can* make them db_owner members, that should clear up the problem.

Moussie
Starting Member

20 Posts

Posted - 2002-08-01 : 08:35:23

quote:

Don't make them sysadmin members. You *can* make them db_owner members, that should clear up the problem.

No it does not. But I think this is a problem with Access.
I only have to change the prefix of the recordsource on a form
to "dbo.my_sp" when that recordsource is a SP, NOT when it is a view.
Also currentproject.connection.execute("exec SP_MYPROC") works
(as does of course currentproject.connection.execute("exec dbo.SP_MYPROC") I think the whole thing boils down to crappy support for SPs in Access 2k.

Moussie

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-01 : 09:03:50

quote:

There is no way I can wade through and change 100s of
forms and recordsources and add "dbo." in front of
them

Why not? Obviously you wouldn't want to do it manually, but can you not just write a procedure that goes through each form inserting dbo. into the appropriate properties?
Ok, it gets a bit of a pain to modify code this way -- when I was trying to build something for examining dependencies in MS Access databases, I had to resort to KeyPress

to get at the code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources