DDL Trigger on newly created database

Question

Post reply

DDL Trigger on newly created database

NJDave

SSCommitted

Points: 1903
More actions
February 15, 2013 at 11:33 am

#270972

Hello
Is there a way to use a DDL trigger to create a user if I don't know the name of the newly created database?
The application dynamically creates a database and I need to add a user. I posted earlier and appreciate the earlier responses - now, I'm not sure how I can write the code if I don't know the name of the database. So far I have...
CREATE TRIGGER t_objectName_event
ON ALL SERVER
AFTER CREATE DATABASE
AS
CREATE_USER /* on the database that was dynamically created */
Thanks in advance
Dave

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

from another post very similar to yours, i created this trigger to create a role if it doesn't exist;

it SEEMS to work in my limited testing then and now:

note this only adds the roles if they database was created as new; not if it was restored from a backup as a new database.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [SERVER_TR_ADD_ROLES_TO_NEW_DBS]

ON ALL SERVER

FOR CREATE_DATABASE

AS

declare @eventData XML;

declare @dbname varchar(100);

set @eventData = EVENTDATA()

SELECT @dbname = @eventData.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME')

--Dynamic SQL so we can get the context?

DECLARE @sql VARCHAR(max)

PRINT 'dbname ' + ISNULL(@dbname,'whoops!')

SET @sql='

USE [THEDBNAME];

--USER_TABLE: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

PRINT db_name() + '': ADDING ROLE_ALLTABLES_FULL ''

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''ROLE_ALLTABLES_FULL'' AND type = ''R'')

CREATE ROLE ROLE_ALLTABLES_FULL

'

SET @sql = REPLACE(@sql,'[THEDBNAME]',@dbname)

PRINT '@sql' + ISNULL(@sql,' double whoops!')

EXECUTE(@sql)

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [SERVER_TR_ADD_ROLES_TO_NEW_DBS] ON ALL SERVER

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

NJDave SSCommitted Points: 1903 More actions · Answer 2

It looks good - I will try it

Thank you very much!

Dave