Security

[Bob McClellan]

Can anyone point me in the right direction to find answers to
Access .adp to SQL Server Security.
We have a mix of MS Access Versions (2k, 2003, 2007) across our east coast
branches.
Because of this, After developing in 2007, I save the production .adp as a
2k version.
I've been having problems with schemas and roles.

I created a role named BranchManagers and a Schema named BranchManagers.
I created table BranchManagers.Employees
and Stored Proc BranchManagers.Employees_View.

I added a user from AD to the Users in Security for the SQL 2005 Server and
mapped him to
the database.

When I log in from his machine with "Use Windows NT Integrated security" I
can see the
table fine. It shows up as Employees (BranchManagers).

When you go to stored procedures though... I see Employees_View (
Administrator ).

The permissions look identical, yet I can not run the sp. I've granted
execute, insert, update... everything just
to try and get it working.

Any help would be very much appreciated.
thanks in advance,
...bob

 

[Sylvain Lafontaine]

First, ADP 2K are very buggy. If possible, you should chose the 2003
format.

For your permission issue, ADP has a lot of trouble for anything that it's
not part of the dbo schema, including user accounts. If you take a look
with the SQL-Server Profiler, probably that you'll see that the username has
been prepended (prefixed?) to the name of the table, view or SP.

Personally, not only I've put everything into the dbo schema but I also take
the precaution of setting the Record Source Qualifier property of every
forms to « dbo ». For a stored procedure that I call explicitely with an
EXEC statement, I will also prefix its name with dbo.

You can try setthing the Record Source Qualifier property to something else
than dbo but quite honestly, ADP has not been designed to work the advanced
schema capabilities of SQL-Server 2005+ and even with SQL-2000, you can run
into a lot of trouble if you try to anything else than dbo.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bob McClellan]

Thanks for the reply Sylvain. Like you, .. I always use dbo.
I also set the qualifier property to dbo. I just keep thinking
optimistically that
there has to be a way to take advantage of the flexibility that roles and
schemas
provide. I handle security a number of different ways. It's easy enough
to check who the user is when they log in and set permissions from within
the app.
...but..
Every time I begin the process of building a new app, I continually
experiment with trying
to get the security working from the SQL side.

thanks again for the reply.
...bob