How to map Windows domain user group to SQL Server 2005 database object permission setting

I was very disappointed when I found out I can’t directly map Windows domain user group to SQL Server 2005 database object permission setting. While you can add a Windows domain user to Security-Logins, and then assign database object permission setting to the Windows domain user account, you can’t do the same to user group. Object permission has to be associated with a user.

I found a doable way to workaround this problem, though. In SQL Server Management Studio:

• For each database I need to create a Database Role, say “MyAppAdminUser”.
• Right click on the Windows domain user group, select Properties. In Login Properties window, select User Mapping. In the “User mapped to this login”, check the database, and then check the Database Role we just create (“MyAppAdminUser”.)
• Finally, configure all database object permission for MyAppAdminUser.

Now whatever right “MyAppAdminUser” gets will be the right the domain user group gets.