Add users to security file from a form

[beacon]

Hi everybody,

I have a database that I intend on setting up user/group permissions for using the built-in security wizard, but I don't want to have to manage adding every user since the database is primarily used by a different department at my company.

I will still end up being the administrator for the database, but I want the users to be able to add new users and assign their permissions, which will be the same for all users, using a form so they won't have to access the security file.

Is this possible?

Thanks,
beacon

[munkee]

This is very much possible beacon, to be honest I have found creating my own security system much easier than using asccess built in security.

However with that being you can use SQL to add users / permissions / groups etc.

You will need to simply tie the following sql procedures to execute within unbound forms.

To create an account sql is

CREATE USER user1 password1 pid1 [, user2 password2 pid2 [, etcetcetc] ]

to create a new group

CREATE GROUP group1 pid1 [, group2 pid2 [, etcetcetc] ]

To change a password you can use

ALTER USER user PASSWORD newpassword oldpassword

To add a user to a group you can use

ADD USER user1 [, user2 [, etcetcetc ]] TO group

To remove a user you can use

DROP USER user1 , [, user2 [, etcetcetc ]] FROM group

To add them a priveladge you can use

GRANT privilege1 [, privilege2 [, etcetcetc ] ] ON {TABLE table1 | OBJECT object1 | CONTAINER container1} TO account1 [, account2 [, etcetcetc]]

Where you select a priveledge clause within the {} brackets above

All of the above referenced from access 2002 desktop developers handbook chapter 5 incase you have it and want some more detail.

[beacon]

This is very much possible beacon, to be honest I have found creating my own security system much easier than using asccess built in security.

However with that being you can use SQL to add users / permissions / groups etc.

You will need to simply tie the following sql procedures to execute within unbound forms.

To create an account sql is

CREATE USER user1 password1 pid1 [, user2 password2 pid2 [, etcetcetc] ]

to create a new group

CREATE GROUP group1 pid1 [, group2 pid2 [, etcetcetc] ]

To change a password you can use

ALTER USER user PASSWORD newpassword oldpassword

To add a user to a group you can use

ADD USER user1 [, user2 [, etcetcetc ]] TO group

To remove a user you can use

DROP USER user1 , [, user2 [, etcetcetc ]] FROM group

To add them a priveladge you can use

GRANT privilege1 [, privilege2 [, etcetcetc ] ] ON {TABLE table1 | OBJECT object1 | CONTAINER container1} TO account1 [, account2 [, etcetcetc]]

Where you select a priveledge clause within the {} brackets above

All of the above referenced from access 2002 desktop developers handbook chapter 5 incase you have it and want some more detail.

Honestly, I prefer to set up my own security too, but this is a database for a different department that needs something setup quickly and due to demands on my time, I really can't afford to create all of the security for them in the timeframe that they need it.

Just so I'm clear, I can add the SQL to a form in the main database and it will update the security file? I'm assuming that a link is created between the main database and the security file when the security file is created and that I won't have to reference that link if I use the SQL you provided in one of my forms...is that right?

[munkee]

For some reason I couldnt get the sql working when I gave it a go last night although I'm sure this was working for me a couple of years ago.

Your 3 options on controlling the security via vba can be found here:

http://msdn.microsoft.com/en-us/library/aa190108%28v=office.10%29.aspx

more direct for using ado which looks quite simple.

http://msdn.microsoft.com/en-us/library/aa165450(v=office.10).aspx

I will look in to further why the sql isnt working and post back as it was really easy to utilise in the past.


Edit:


Cracked it I forgot you had to use ADO to be able to use Jet 4 sql commands. Just create the string adding in any elements needed from your form.

Private Sub cmdClick()
Dim conDatabase As ADODB.Connection
Dim SQL As String

On Error GoTo ErrorsofMassDEATH

Set conDatabase = Application.CurrentProject.Connection
SQL = "CREATE USER user1 password1 pid1"
conDatabase.Execute SQL

conDatabase.Close
Set conDatabase = Nothing

Exit Sub
ErrorsofMassDEATH
   MsgBox Err.Description, vbInformation
Exit Sub
End Sub