CREATE_DATABASE Trigger to add a user

Question

Post reply

CREATE_DATABASE Trigger to add a user

Justin-281851

SSC-Addicted

Points: 434
More actions
February 2, 2012 at 8:13 pm

#263908

I'm trying to create a trigger that will add a user into the db_datareader role and I have my code below but it isn't working. What am I doing wrong and also how would I code this to have this trigger only fire if a certain naming convention is used for the a new database? For example the trigger will only fire when a database that has the name 'MY_DATABASE%' is created.

/****** Object: DdlTrigger [Trigger_DB_Creation] Script Date: 02/02/2012 21:51:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [Trigger_DB_Creation] ON ALL SERVER

FOR CREATE_DATABASE

AS

CREATE USER FOR LOGIN

GO

EXEC sp_addrolemember N'db_datareader', N'USER'

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [Trigger_DB_Creation] ON ALL SERVER

GO

Viewing 6 posts - 1 through 5 (of 5 total)

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

ElSuket SSChasing Mays Points: 607 More actions · Answer 1

Hello,

Here you go :

ALTER TRIGGER [Trigger_DB_Creation]

ON ALL SERVER

FOR CREATE_DATABASE

AS

BEGIN

SET NOCOUNT ON

DECLARE @event_data xml = EVENTDATA()

, @database_name sysname

, @sql nvarchar(1024)

SELECT @database_name = @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname')

SET @sql = 'USE ' + @database_name + ';'

SET @sql = @sql + 'CREATE USER [myUser] FOR LOGIN [myLogin];'

SET @sql = @sql + 'EXEC sp_addrolemember ''db_datareader'', ''myUser'''

EXEC (@sql)

END

Justin-281851 SSC-Addicted Points: 434 More actions · Answer 2

What would be causing this error?

SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. (Microsoft SQL Server, Error: 1934)