Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Trigger to map users when DB is created

Author  Topic 

reepcore
Starting Member

3 Posts

Posted - 2015-05-08 : 01:15:14
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.

Kristen
Test

22859 Posts

Posted - 2015-05-08 : 04:18:41
I expect you can do this, instad of using USE

EXEC [MyDB].dbo.sp_addrolemember N'db_owner', N'S\G1'

You can't have the GO statements in your TRIGGER. The first one will "finish" the TRIGGER CREATE
Go to Top of Page
   

- Advertisement -