Trigger to map users when DB is created

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources