Trigger to apply user mapping to logins on database creation Does anyone have any example triggers that apply user mapping on database creation?
Here's a very quick example that will create a user blat given an existing login blat in all new databases created (this won't work for attach/restore). You could extend this to adding them to roles, granting permissions, even building the list based off of some query from sys.server_principals in master, sys.database_principals in another database, or your own users table. Note that it doesn't have any error handling.

CREATE TRIGGER DBCreate_MapUsers
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
  DECLARE @db NVARCHAR(510);
  SET @db = CONVERT(SYSNAME, EVENTDATA().query('(/EVENT_INSTANCE/DatabaseName[1]/text())'));
  SET @db = QUOTENAME(@db) + N'.sys.sp_executesql ';
  EXEC @db N'CREATE USER blat FROM LOGIN blat;';
END