Managing Permissions on a server with new DBs being added I'm currently dealing with a database where multiple databases will be added in the future. Right now, I'm working on creating some users with specific permissions and I'm struggling with how to ensure that they can also cover the databases that aren't yet added. Complicating things, most of these permissions are needed only databases ending in a specific suffix. 

For example, a user needs to be created that can add assemblies to all the current and future databases that end in ABC. 

Is there a way to make this easier to manage, or will it involve always ensuring after a new set of DBs are added that the user has access to the ones ending in ABC?
One possible option would be to:


Create a Database Role in each DB (i.e. this should be in your template / source DB) that contains the desired set of permissions.
Create an Instance / Server -level DDL Trigger that fires on the CREATE_DATABASE event and does the following:


Checks the name of the Database
If new DB name ends with ABC then:


Adds the Login to the new Database as a User
Add the new User to the Database Role that has the desired permissions




This will likely require some Dynamic SQL, but it should work. And honestly, I'm not sure what I think of using a DDL Trigger in this manner, but it certainly does automate it ;-).