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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Trigger to map users when DB is created
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

reepcore
Starting Member

United Kingdom
3 Posts

Posted - 05/08/2015 :  01:15:14  Show Profile  Reply with Quote
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.

Edited by - reepcore on 05/08/2015 01:56:48

Kristen
Test

United Kingdom
22859 Posts

Posted - 05/08/2015 :  04:18:41  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000