SQL Permissions

I have an MS Access front end which is linking to an SQL database (2008 R2). How can I assign permissions to all employees without having to do it user-by-user? Users are accessing this app on a terminal server via RDP using AD credentials to log on. Thanks for your assistance.
LVL 11
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You should discuss this with your DBA.  Does he want individual logins?  Or can he live with not being able to tell who is truly connected to the database.  If he can live with a generic connection, then create a single userID with the necessary permissions.  Delete all the linked tables and link them again with the new credentials.  Check the box to save the password.  You will be warned for each link.  Of course most DBA's frown on storing the UID and password with the link since Access stores them in plain text.  So, you may want to compromise and not save the password.  But this means that either your user will get prompted every time he opens the app or you will need to intervene and refresh the link and pass the password when the app opens.

When a DBA wants individual logins, I generally  use a single password that only the DBA and I know.  We never tell the users.  When the user logs into the app, I supply the password from code and refresh for him before opening the switchboard.  What this does is to prevent the users who are tech savvy from being able to open Access and create their own links to the BE.  They know their ID but never know their password so they can't bypass my app to get to the data directly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Create an group for the users in Active Directory, add the users to the group and then grant the AD group access to the database in SQL Servier.  The first answer to this post indicates how to grant permissions to an AD group in SQL Server 2008
Deepak ChauhanSQL Server DBACommented:
If you are not comfirtable to create group in AD you can create a Windows group in the database server and add all required AD users in this group then add the group in SQL server logins.

Once group is added in SQL logins map it with the databases which you want to accessible to users.

1. Create local windows group on databases server.
2. Add AD users in this group.
3. Add this group in SQL login using ssms. (This way a group login will be created)
4. Go to group login property and map with the databases using ssms. (just check mark the database names)
5. Assign permission like DB_reader or DB_writer or DB_Owner .... as per need.

If you grant sysadmin role to this group login the no need to map and assign permisssion, all users within the group can access any database. But this is not a secure way as they can change anything.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I also would never chose a single SQL Server login. Will prefer to give permission to an AD group and request to the AD administrator to add the users to that group. It's more secure and much simple to manage. I even wrote an article about that if you want to give a read: SQL Server Security: Server authentication mode
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.