Recommendation for MS Access Admin Database for SQL Server User Management

[Gaztry80]

Hello everyone,

I have been working with MS Access linked to SQL Server for a while now, but only as a single user until recently. I am now experimenting with a setup for multiple users, which brings new challenges in terms of access and management.

Currently, I have set up a user database with only linked tables and views. At the moment, I run SQL scripts in SSMS to manage permissions and table/view access. This approach is becoming difficult to manage and could easily become disorganized, especially since I do not work with it daily. Therefore, I am considering creating an admin database in Access to centralize user management, making it easier to control roles and permissions.

I could build such an admin database from scratch, but I imagine there are templates available that could help me avoid common pitfalls. I am curious to know if anyone knows a template they can recommend for inspiration, specifically in combination with Azure SQL Server.

Of course, I could also set up group roles, and maybe some would even say that Access is not the best choice for an admin application for this purpose. I am open to any feedback or advice.

Thank you in advance for any tips.

 

[arnelgp]

you already doing it in ssms, which is more secured than ms access.

 

[arnelgp]

This approach is becoming difficult to manage and could easily become disorganized, especially since I do not work with it daily.

being organized is a must.
you must document (logbook) whatever you do with ssms.
therefore if you got lost, the book will be your guide to what things
have been done and what access have been granted to which person.

 

[GPGeorge]

Hello everyone,

I have been working with MS Access linked to SQL Server for a while now, but only as a single user until recently. I am now experimenting with a setup for multiple users, which brings new challenges in terms of access and management.

Currently, I have set up a user database with only linked tables and views. At the moment, I run SQL scripts in SSMS to manage permissions and table/view access. This approach is becoming difficult to manage and could easily become disorganized, especially since I do not work with it daily. Therefore, I am considering creating an admin database in Access to centralize user management, making it easier to control roles and permissions.

I could build such an admin database from scratch, but I imagine there are templates available that could help me avoid common pitfalls. I am curious to know if anyone knows a template they can recommend for inspiration, specifically in combination with Azure SQL Server.

Of course, I could also set up group roles, and maybe some would even say that Access is not the best choice for an admin application for this purpose. I am open to any feedback or advice.

Thank you in advance for any tips.

At our recent AUG meeting Ron McCarry presented his approach to managing cached credentials for ODBC linked tables. It includes discussion of the way he manages users and logins. Perhaps it can give you some ideas.