Converting MDW protected database to SQL Server

[Mark Flippin]

I'm converting the backend of an Access 2000 database to SQL Server
2000.

The existing database has user and group security through a specific
workgroup file.

Under the "user and group accounts" there is a button to print the
users and group relationships. As there are only about 50 authorized
users and 6 groups, this has worked.

But under the "user and group permissions" there is no report option;
just the ability to select every possible combination of
user/group/object and view the permissions.

I'm looking for a way to report on permissions on each object (form,
table, report, query, group, user) to provide the basis of the design
of reasonable security setup on SQL Server and identify areas of the
existing front end to be analyzed for problems in the conversion

Any help would be appreciated.

Mark Flippin

[David Lloyd]

Mark:

The Access security model is available through DAO. You could
programmatically iterate through all users and groups, and their appropriate
permissions on each database object. You could store the results to a table
or text file.

F. Scott Barker's "Microsoft Access 2000 Power Programming" has an excellent
chapter on how to programmatically access the security features of Access.
(There may be a newer edition of this book).

An example would be the following:

Dim db as Database
Dim con as Container
Dim doc as Document

Set db = CurrentDb
Set con = db.Containers("Tables")
Set doc = con.Documents("MyTable")

doc.UserName = "MyUser"

Debug.Print doc.AllPermissions

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"Mark Flippin" <me******@comcast.net> wrote in message
news:ha********************************@4ax.com...

I'm converting the backend of an Access 2000 database to SQL Server
2000.

The existing database has user and group security through a specific
workgroup file.

Under the "user and group accounts" there is a button to print the
users and group relationships. As there are only about 50 authorized
users and 6 groups, this has worked.

But under the "user and group permissions" there is no report option;
just the ability to select every possible combination of
user/group/object and view the permissions.

I'm looking for a way to report on permissions on each object (form,
table, report, query, group, user) to provide the basis of the design
of reasonable security setup on SQL Server and identify areas of the
existing front end to be analyzed for problems in the conversion

Any help would be appreciated.

Mark Flippin

[Mark Flippin]

David

Thanks for the post. This info looks familiar: I remember doing
something similar a few years ago and probably have the code,

I'll go down this path

Thanks again
Mark

This response is supplied with my gratitude without any
representations or warranties.

On Wed, 18 May 2005 08:44:59 -0400, "David Lloyd"
<Ne********@LemingtonConsulting.com> wrote:

Mark:

The Access security model is available through DAO. You could
programmatically iterate through all users and groups, and their appropriate
permissions on each database object. You could store the results to a table
or text file.

F. Scott Barker's "Microsoft Access 2000 Power Programming" has an excellent
chapter on how to programmatically access the security features of Access.
(There may be a newer edition of this book).

An example would be the following:

Dim db as Database
Dim con as Container
Dim doc as Document

Set db = CurrentDb
Set con = db.Containers("Tables")
Set doc = con.Documents("MyTable")

doc.UserName = "MyUser"

Debug.Print doc.AllPermissions