Using code to change object permissions

[fishonland]

I need a solution for allowing a user to change permissions for all Tables/Queries/Forms

One Problem
1. The user doesn't have security permissions for changing user/group permissions. (This must stay like this)

Any Help would be great.

 

[Estuardo]

Hello fishonland,

I guess that what you're trying to do is not possible... Unless you hack the users file. What i honestly wouldn't attempt to do.

Regards

Estuardo

 

[fishonland]

I don't need to "hack" the users file, I do have the workgroup file with administrator rights, however the users don't have access to this information (security reasons) and don't have enough rights to change permissions.

Just to clarify,
I would like to be able to create a script file that uses the "administrator" User information (Invisible to the user) to update the permissions for all the objects in a database.

 

[fishonland]

I have been working on this script to resolve my problem. The problem comes up when I try to pass on a database and workgroup into it I get an error on the "cnn.Open "data source=...." line.

Run-time error -2147217843'
Not a valid account name or password.

The username and password that I have entered is valid when I open the database manually (I have changed the username and password in this post). My guess is that I am having problems with the ADODB Connection String.

For Reference I am running WinXP Pro (SP-1) Access 2000 & Access 97.

Thanks

Public Function ChangeSecurityLevel(DbName As String, WgName As String, DbObj As String)

'*** Changes the Security Level of Table or Query that are passed through ***
Dim UserName1 As String 'Name of Master user
Dim UserPass1 As String 'Password of Master user
Dim ChangeUser As String 'Name of user to change access rights
Dim cnn As New ADODB.Connection
'Dim objAcc As Access.Application
Dim cat As New ADOX.Catalog
Dim lngPerm As Long

'set information
UserName1 = "AdminUser"
UserPass1 = "password"
ChangeUser = "admin"

'Specify connection string
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Open "data source=" & DbName & ";jet oledb:system database=" & WgName, UserName1, UserPass1

Set cat.ActiveConnection = cnn

' Retrieve original permissions
lngPerm = cat.Users(ChangeUser).GetPermissions(DbObj, adPermObjTable)
Debug.Print "Original permissions: " & Str(lngPerm)

' Give the User rights on the specified object
cat.Users(ChangeUser).SetPermissions DbObj, adPermObjTable, _
adAccessSet, adRightRead + adRightDelete + adRightUpdate + adRightInsert

' Display permissions
Debug.Print "Final permissions: " & _
Str(cat.Users(ChangeUser).GetPermissions(DbObj, adPermObjTable))

cnn.Close


End Function