alenknight
asked on
DDL Trigger for creating logons and users
I am trying to create a DDL trigger that when a user or logon is created on either server or ANY DB... I'd like it to fire off an email showing who was added to the server/db.
Any recommendations how to do this?
would i have to create a trigger for each db? (sp_msforeachdb?)
thanks
alex
Any recommendations how to do this?
would i have to create a trigger for each db? (sp_msforeachdb?)
thanks
alex
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yeah... looks like it might work.... this is for 2005, that i'm requesting.
Here's the code you'll need to use. You'll have to run the database trigger in every database since the sp_msforeachdb procedure doesn't allow the keyword GO, and this must be used after USE ? GO in the procedure.
USE master
GO
CREATE TRIGGER DDL_Prevent_Create_Login
ON ALL SERVER
FOR CREATE_LOGIN
AS
RAISERROR ('Cannot add login!',10, 1)
ROLLBACK
GO
USE DatabaseName
GO
CREATE TRIGGER DDL_Prevent_Create_User
ON DATABASE
FOR CREATE_USER
AS
RAISERROR ('Cannot add user!',10, 1)
ROLLBACK
GO
Hi,
Just a thought: Ive seen use statements in sp_MSForEachDB and execute statements without the go, and I haven't seen a problem.
Have you tried this without the go?
Cheers
David
Just a thought: Ive seen use statements in sp_MSForEachDB and execute statements without the go, and I haven't seen a problem.
Have you tried this without the go?
Cheers
David
Yes, you can have USE in that procedure, but you cannot have GO. Since CREATE TRIGGER must be the first statement in a batch, you must put a GO after USE databasename. Since you must specify USE ? GO in the procedure for the trigger to be created in every database, the procedure will throw an error. Give it a shot.
EXEC sp_msforeachdb 'USE ?
GO
CREATE TRIGGER DDL_Prevent_Create_User
ON DATABASE
FOR CREATE_USER
AS
RAISERROR (''Cannot add user!'',10, 1)
ROLLBACK'
EXEC sp_msforeachdb 'USE ?
GO
CREATE TRIGGER DDL_Prevent_Create_User
ON DATABASE
FOR CREATE_USER
AS
RAISERROR (''Cannot add user!'',10, 1)
ROLLBACK'
Okay, I stand corrected.
One thought for using the sp_msforeachdb is to produce the code itself like this:
use tempdb
go
EXEC sp_msforeachdb 'select ''USE ?
GO
CREATE TRIGGER DDL_Prevent_Create_User
ON DATABASE
FOR CREATE_USER
AS
RAISERROR (''''Cannot add user!'''',10, 1)
ROLLBACK''
'
;
Produces results something like this :
--------------------------
USE master
GO
CREATE TRIGGER DDL_Prevent_Create_User
ON DATABASE
FOR CREATE_USER
AS
RAISERROR ('Cannot add user!',10, 1)
ROLLBACK
--------------------------
USE tempdb
GO
CREATE TRIGGER DDL_Prevent_Create_User
ON DATABASE
FOR CREATE_USER
AS
RAISERROR ('Cannot add user!',10, 1)
ROLLBACK
etc ...
Cheers
David
One thought for using the sp_msforeachdb is to produce the code itself like this:
use tempdb
go
EXEC sp_msforeachdb 'select ''USE ?
GO
CREATE TRIGGER DDL_Prevent_Create_User
ON DATABASE
FOR CREATE_USER
AS
RAISERROR (''''Cannot add user!'''',10, 1)
ROLLBACK''
'
;
Produces results something like this :
--------------------------
USE master
GO
CREATE TRIGGER DDL_Prevent_Create_User
ON DATABASE
FOR CREATE_USER
AS
RAISERROR ('Cannot add user!',10, 1)
ROLLBACK
--------------------------
USE tempdb
GO
CREATE TRIGGER DDL_Prevent_Create_User
ON DATABASE
FOR CREATE_USER
AS
RAISERROR ('Cannot add user!',10, 1)
ROLLBACK
etc ...
Cheers
David
reb73:
That blog looks interesting. Is this for SQL 2005 or SQL 2008?
Cheers
David