I have a lot of SQL 2005 servers with a lot of databases and a lot of Windows users that need access to all the servers but only a few databases, and that last part changes regularly. Managing these separately is messy. What I'd like to do:
- Add all my relevant windows users to an Active Directory Group called "DB_ACCESS"
- Add a group login to all my SQL servers for MYDOMAIN\DB_ACCESS
- Grant individual members of that group permission to individual databases when needed
I tested it in the most obvious configuration (added a user to DB_ACCESS, added DB_ACCESS to the server's list of logins with a default role of public, added the individual user to a database) and the user could login to the server but not access the database.
Is this possible? Am I just missing a key configuration item?