I have a doubt regarding granting database level roles to the created SQL Login on the newly created databases in our environment. Is it somehow possible to automate this?? Meaning my requirement is to grant the SQL login a database role (db_owner, db_datawriter, etc) to databases as soon as they are created.
As per my knowledge there are two ways:
1. Making changes to model: Not preferred since this will affect all the other databases as well.
2. Using DDL Trigger: This I prefer since we have certain naming conventions in our environment and this rule should be applied only to them.
Doing some research on the internet, I found this piece of code:
CREATE TRIGGER trg_DDL_CreateDatabase
ON ALL SERVER
DECLARE @databaseName sysname;
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)', 'sysname'));
declare @sql nvarchar(max);
set @sql = N'USE ' + quotename(@databaseName) + N';
IF IS_MEMBER (''DOMAIN\GROUP'') = 1
EXEC sp_addrolemember N''db_owner'', N''DOMAIN\SOMEGROUP''
EXEC sp_addrolemember N''db_owner'', N''DOMAIN\SOMEOTHERGROUP'';';
exec sp_executesql @sql;
I did not try this since I want to add naming constraint to this query. I'm not well versed with triggers concept. Kindly help me on this and give your suggestions.