I am looking to create a trigger that will map users when a specific database is created.
I.e.
I have groups 's\g1', 's\g2', 's\g3', 's\g4' and when DB 'MyDB' is created I want the group to be mapped to this DB and the dbowner role membership assigned.
USE master
GO
CREATE TRIGGER [ddl_trig_database_TEST]
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @databaseName VARCHAR(255)
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))
IF @databaseName = 'MyDB'
USE [MyDB]
EXEC sp_addrolemember N'db_owner', N'S\G1'
GO
EXEC sp_addrolemember N'db_owner', N'S\G2'
GO
EXEC sp_addrolemember N'db_owner', N'S\G3'
GO
EXEC sp_addrolemember N'db_owner', N'S\G4'
GO
The problem is 'use' cannot be used in a trigger.
Any ideas?
Thanks in advance.
Trigger to map users when DB is created
Posted - 05/08/2015 : 01:15:14
