Managing SQL Server logins and database users

Question

I am getting a little further into the database administration side of things and starting to set up security access to SQL server databases for users. I have a question about server logins and database users in the following scenario:

We have 30 database users that require the same permissions, each with their own AD accounts.

When setting up SQL server access for these SQL writers what is the most performant approach for giving a group of users with the same permissions access to a server/database?

I appreciate that this could be achieved via several approaches but am keen to learn the performant approach to managing relationships between server logins/database users/database roles.

Thanks :)

Answer 1

The easiest and most manageable way to achieve this is using Active Directory groups.

Have your ad administrator create a security group called SQL_Writers (or something similar) for those users and add them (and any new users to that group).

Then in SQL Server, simply grant that security group the appropriate permissions to the server / databases.

If you want the permissions to be inherited to any newly created database on your instance, remember to grant the security role to the model database as well.