February 2, 2012 at 8:13 pm
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
February 2, 2012 at 9:06 pm
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
February 2, 2012 at 9:12 pm
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)
February 2, 2012 at 11:17 pm
see link elow for general triger info
February 3, 2012 at 6:16 am
I added
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
and I'm still getting the error. Any thoughts?
February 3, 2012 at 11:13 am
Is there a way to have the trigger only fire when certain naming convention is used for a new database?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply