dbo schema problem and permissions

[Ruggles]

Some 10 years ago I developed an Access adp multi-user application for
a small business. The application makes extensive use of stored
procedures, as well as sql strings views and user functions. It's
worked fine for nearly 10 years. Okay, now, without consulting me,
the company decided to skip right to SQL 2008! When I open the ap, it
works fine (I am of course a sys admin). When a user opens it, it
fails. When I look at the tables, views and sprocs in the user adp
file, they all have "dbo" appended to the object's name. When I open
the app up as the sys admin no such "dbo" appendage appears. When I
temporarily make a user a sys admin, then the "dbo" disappears and
everything works fine. The forms make extensive use of sprocs as data
sources, and I use sql strings throughout the application, none of
which are fully qualified with the "dbo" schema (as I said, I did this
application 10 years ago).

So, short of making every user a member of the sys admin role, and
short of going through the entire application and making sure all my
sql strings and stored procedures are fully qualified, is there some
security role or group I can assign users to that will allow this
application to work like it does with SQL 2000?

--John

 

[Jennifer]

Some 10 years ago I developed an Access adp multi-user application for
a small business.  The application makes extensive use of stored
procedures, as well as sql strings views and user functions.  It's
worked fine for nearly 10 years.  Okay, now, without consulting me,
the company decided to skip right to SQL 2008!  When I open the ap, it
works fine (I am of course a sys admin).  When a user opens it, it
fails.  When I look at the tables, views and sprocs in the user adp
file, they all have "dbo" appended to the object's name.  When I open
the app up as the sys admin no such "dbo" appendage appears.  When I
temporarily make a user a sys admin, then the "dbo" disappears and
everything works fine.  The forms make extensive use of sprocs as data
sources, and I use sql strings throughout the application, none of
which are fully qualified with the "dbo" schema (as I said, I did this
application 10 years ago).

So, short of making every user a member of the sys admin role, and
short of going through the entire application and making sure all my
sql strings and stored procedures are fully qualified, is there some
security role or group I can assign users to that will allow this
application to work like it does with SQL 2000?

--John

If you don't mind giving all users read access to all tables, I'd say
try adding them all to db_datareader role and give execute permissions
on dbo. This sounds similar to the problem I am having.