DDL triggers on create Logins

Hi all,
I have setup a DDL trigger for server logins. If I drop a user my trigger fires off an email with the EVENTDATA().value. If I create a new user the Eventdata is null. I am using the code below:
TIA,
Joe

DROP TRIGGER ddl_trig_login
ON ALL SERVER
GO
CREATE TRIGGER ddl_trig_login
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
declare @.user varchar(100),@.event varchar(1000), @.subj varchar(100)
select @.user = SUSER_SNAME()
Select @.subj = 'Login Event Issued from '+@.user
select @.event = @.user+' committed the following event on ENSQLD1_2005: '+
isnull((SELECT EVENTDATA().value'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')),' ')+' Please email the group with the SIR# for this action.'
EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'SQL2005mail',
@.recipients = 'j.f@.myco.com',
@.body = @.event,
@.subject = @.subj
;

TSQLCommand text is not available for CREATE LOGIN, so you won't be able to see the data. But you can always see other useful information through EVENTDATA.value(). For example

select @.event = @.user+' committed the following event on ENSQLD1_2005: '+
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') +
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') +
...

|||Thank you,
I was able to trap the new user name and the create login eventtype. Now I have the triggers emailing me on any drop of user or create of one.

Do you know what event gets fired off when I change the permissions of a user? I thought it would be Alter_login, but when I grant rights to a DB to a user I dont get an email.

Thanks again!
Joe|||

> Do you know what event gets fired off when I change the permissions of a

> user?

Well, did you try running profiler and inspect the commands sent?

DDL Triggers

I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?

I have some code here (http://sqlservercode.blogspot.com/2006/08/ddl-trigger-events-revisited.html) that basically shows that you can combine events

But where is this info in BOL?

For example if I do this:

create a trigger and I use DDL_VIEW_EVENTS

CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_VIEW_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents" to drop, create or alter Views!'
ROLLBACK;
GO

After that I would check the sys.triggers and sys.trigger_events views to see what was inserted

SELECT name,te.type,te.type_desc
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc

In this case 3 rows were inserted

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

So here is the complete list for who wants it

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_XML_SCHEMA_COLLECTION_EVENTS
-
177 CREATE_XML_SCHEMA_COLLECTION
178 ALTER_XML_SCHEMA_COLLECTION
179 DROP_XML_SCHEMA_COLLECTION

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_TRIGGER_EVENTS
-
71 CREATE_TRIGGER
72 ALTER_TRIGGER
73 DROP_TRIGGER

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_TYPE_EVENTS
-
91 CREATE_TYPE
93 DROP_TYPE

DDL_TABLE_EVENTS
-
21 CREATE_TABLE
22 ALTER_TABLE
23 DROP_TABLE

DDL_SYNONYM_EVENTS
-
34 CREATE_SYNONYM
36 DROP_SYNONYM

DDL_STATISTICS_EVENTS
--
27 CREATE_STATISTICS
28 UPDATE_STATISTICS
29 DROP_STATISTICS

DDL_SERVICE_EVENTS

161 CREATE_SERVICE
162 ALTER_SERVICE
163 DROP_SERVICE

DDL_SCHEMA_EVENTS

141 CREATE_SCHEMA
142 ALTER_SCHEMA
143 DROP_SCHEMA

DDL_ROUTE_EVENTS

164 CREATE_ROUTE
165 ALTER_ROUTE
166 DROP_ROUTE

DDL_ROLE_EVENTS
-
134 CREATE_ROLE
135 ALTER_ROLE
136 DROP_ROLE

DDL_REMOTE_SERVICE_BINDING_EVENTS
--
174 CREATE_REMOTE_SERVICE_BINDING
175 ALTER_REMOTE_SERVICE_BINDING
176 DROP_REMOTE_SERVICE_BINDING

DDL_QUEUE_EVENTS

157 CREATE_QUEUE
158 ALTER_QUEUE
159 DROP_QUEUE

DDL_PROCEDURE_EVENTS
-
51 CREATE_PROCEDURE
52 ALTER_PROCEDURE
53 DROP_PROCEDURE

DDL_PARTITION_SCHEME_EVENTS

194 CREATE_PARTITION_SCHEME
195 ALTER_PARTITION_SCHEME
196 DROP_PARTITION_SCHEME

DDL_PARTITION_FUNCTION_EVENTS

191 CREATE_PARTITION_FUNCTION
192 ALTER_PARTITION_FUNCTION
193 DROP_PARTITION_FUNCTION

DDL_EVENT_NOTIFICATION_EVENTS
-
74 CREATE_EVENT_NOTIFICATION
76 DROP_EVENT_NOTIFICATION

DDL_ASSEMBLY_EVENTS
--
101 CREATE_ASSEMBLY
102 ALTER_ASSEMBLY
103 DROP_ASSEMBLY

DDL_CONTRACT_EVENTS
--
154 CREATE_CONTRACT
156 DROP_CONTRACT

DDL_FUNCTION_EVENTS

61 CREATE_FUNCTION
62 ALTER_FUNCTION
63 DROP_FUNCTION

DDL_INDEX_EVENTS

24 CREATE_INDEX
25 ALTER_INDEX
26 DROP_INDEX
206 CREATE_XML_INDEX

DDL_MESSAGE_TYPE_EVENTS

151 CREATE_MESSAGE_TYPE
152 ALTER_MESSAGE_TYPE
153 DROP_MESSAGE_TYPE

Denis The SQL Menace

http://sqlservercode.blogspot.com

Hi Denis,

This information is documented in the Books Online topic "Event Groups for Use with DDL Triggers.

http://msdn2.microsoft.com/en-us/library/ms191441.aspx

Regards,

Gail

|||

Thank you, however I would prefer text over an image (So that I can work my copy and paste magic!!)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

DDL Triggers

I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?

I have some code here (http://sqlservercode.blogspot.com/2006/08/ddl-trigger-events-revisited.html) that basically shows that you can combine events

But where is this info in BOL?

For example if I do this:

create a trigger and I use DDL_VIEW_EVENTS

CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_VIEW_EVENTS
AS
PRINT'You must disable Trigger "ddlTestEvents" to drop, create or alter Views!'
ROLLBACK;
GO

After that I would check the sys.triggers and sys.trigger_events views to see what was inserted

SELECT name,te.type,te.type_desc
FROMsys.triggers t
JOINsys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc

In this case 3 rows were inserted

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

So here is the complete list for who wants it

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_XML_SCHEMA_COLLECTION_EVENTS
-
177 CREATE_XML_SCHEMA_COLLECTION
178 ALTER_XML_SCHEMA_COLLECTION
179 DROP_XML_SCHEMA_COLLECTION

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_TRIGGER_EVENTS
-
71 CREATE_TRIGGER
72 ALTER_TRIGGER
73 DROP_TRIGGER

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_TYPE_EVENTS
-
91 CREATE_TYPE
93 DROP_TYPE

DDL_TABLE_EVENTS
-
21 CREATE_TABLE
22 ALTER_TABLE
23 DROP_TABLE

DDL_SYNONYM_EVENTS
-
34 CREATE_SYNONYM
36 DROP_SYNONYM

DDL_STATISTICS_EVENTS
--
27 CREATE_STATISTICS
28 UPDATE_STATISTICS
29 DROP_STATISTICS

DDL_SERVICE_EVENTS

161 CREATE_SERVICE
162 ALTER_SERVICE
163 DROP_SERVICE

DDL_SCHEMA_EVENTS

141 CREATE_SCHEMA
142 ALTER_SCHEMA
143 DROP_SCHEMA

DDL_ROUTE_EVENTS

164 CREATE_ROUTE
165 ALTER_ROUTE
166 DROP_ROUTE

DDL_ROLE_EVENTS
-
134 CREATE_ROLE
135 ALTER_ROLE
136 DROP_ROLE

DDL_REMOTE_SERVICE_BINDING_EVENTS
--
174 CREATE_REMOTE_SERVICE_BINDING
175 ALTER_REMOTE_SERVICE_BINDING
176 DROP_REMOTE_SERVICE_BINDING

DDL_QUEUE_EVENTS

157 CREATE_QUEUE
158 ALTER_QUEUE
159 DROP_QUEUE

DDL_PROCEDURE_EVENTS
-
51 CREATE_PROCEDURE
52 ALTER_PROCEDURE
53 DROP_PROCEDURE

DDL_PARTITION_SCHEME_EVENTS

194 CREATE_PARTITION_SCHEME
195 ALTER_PARTITION_SCHEME
196 DROP_PARTITION_SCHEME

DDL_PARTITION_FUNCTION_EVENTS

191 CREATE_PARTITION_FUNCTION
192 ALTER_PARTITION_FUNCTION
193 DROP_PARTITION_FUNCTION

DDL_EVENT_NOTIFICATION_EVENTS
-
74 CREATE_EVENT_NOTIFICATION
76 DROP_EVENT_NOTIFICATION

DDL_ASSEMBLY_EVENTS
--
101 CREATE_ASSEMBLY
102 ALTER_ASSEMBLY
103 DROP_ASSEMBLY

DDL_CONTRACT_EVENTS
--
154 CREATE_CONTRACT
156 DROP_CONTRACT

DDL_FUNCTION_EVENTS

61 CREATE_FUNCTION
62 ALTER_FUNCTION
63 DROP_FUNCTION

DDL_INDEX_EVENTS

24 CREATE_INDEX
25 ALTER_INDEX
26 DROP_INDEX
206 CREATE_XML_INDEX

DDL_MESSAGE_TYPE_EVENTS

151 CREATE_MESSAGE_TYPE
152 ALTER_MESSAGE_TYPE
153 DROP_MESSAGE_TYPE

Denis The SQL Menace

http://sqlservercode.blogspot.com

Hi Denis,

This information is documented in the Books Online topic "Event Groups for Use with DDL Triggers.

http://msdn2.microsoft.com/en-us/library/ms191441.aspx

Regards,

Gail

|||

Thank you, however I would prefer text over an image (So that I can work my copy and paste magic!!)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

DDL Trigger and drop table causes error

I have a DDL Trigger that writes a record into a table when any DDL event happens.

It works great except when I try dropping any object. I get an error at that point and the object is not dropped. The trigger and error are listed below.

Thanks.

J

Trigger:

/****** Object: DdlTrigger [MonitorDBChanges] Script Date: 07/24/2007 11:02:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [MonitorDBChanges]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

set nocount on

declare @.EventType varchar(100)

declare @.SchemaName varchar(100)

declare @.ObjectName varchar(100)

declare @.ObjectType varchar(100)

declare @.Script varchar(2000)

SELECT

@.EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')

,@.SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')

,@.ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')

,@.ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')

,@.Script = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

-- Is the default schema used

if @.SchemaName = ' ' select @.SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p

on u.uid = p.principal_id where u.name = CURRENT_USER

insert into MonitorDBChanges

select @.EventType, @.SchemaName, @.ObjectName, @.ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(),@.Script

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [MonitorDBChanges] ON DATABASE

*******************************************************************************

Error Msg:

SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Microsoft SQL Server, Error: 1934)

Have you tried placing SET ARITHABORT ON along with the ANSI_NULLS and QUOTED_IDENTIFIER options?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Tried that but I am going to give it another try.|||

Confirmed. No difference

Any other ideas?

DDL in Transactions

Hello,
I know that it is possible to put DDL statements (i.e.
CREATE TABLE, DROP TABLE etc...) in transactions but I
have found a peculiarity that I am trying to get around.
I issued the following:
BEGIN TRANSACTION
CREATE VIEW TempView AS select * from tempTable
COMMIT TRANSACTION
It gave the following error message:
Server: Msg111, level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch
Can anyone find a way around this using the simple T-SQL
code above?
Thanks in advance
Jamie
P.S. Why is there no microsoft.public.sqlserver.tsql
newsgroup?Hello Jamie !
Sorry but this aint the way it goes. DDL Statements such as
alter,create,drop fires an Implicit commit to send the changes directly to
the database.
HTH, Jens Süßmeyer.|||Jens,
Thats what I always thought too. But if I try this:
BEGIN TRANSACTION
create table temptable (col1 int)
ROLLBACK TRANSACTION
the rollback works (i.e. the table isn't created). Try it! There is even a server level setting that indicates whether you can allow DDL in transactions or not (see sp_server_info, number 110).
So, I can have DDL in a transaction but not CREATE VIEW it seems. Why not?
Regards
Jamie
>--Original Message--
>Hello Jamie !
>Sorry but this aint the way it goes. DDL Statements such as
>alter,create,drop fires an Implicit commit to send the changes directly to
>the database.
>HTH, Jens S=FC=DFmeyer.
>
>.
>|||Hi Jens,
That is not true, DDL does not do an implicit commit and can be included in
a multi statement transaction. The only issue there is, is the error Jamie
got: CREATE VIEW/PROCEDURE and a few others have to be the first statement
in a batch. There is an easy way around that, as a transaction can span
multiple batches:
BEGIN TRAN
GO
CREATE VIEW....
COMMIT TRAN
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
> Hello Jamie !
> Sorry but this aint the way it goes. DDL Statements such as
> alter,create,drop fires an Implicit commit to send the changes directly to
> the database.
> HTH, Jens Süßmeyer.
>|||DDL does not issue an implicit commit in SQL Server, although this may
be the case with some other RDBMS vendors.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
> Hello Jamie !
> Sorry but this aint the way it goes. DDL Statements such as
> alter,create,drop fires an Implicit commit to send the changes
directly to
> the database.
> HTH, Jens Süßmeyer.
>|||DDL for textual objects (views, procedures, etc.) must be in a separate
batch so that SQL Server can determine where the CREATE statement ends.
Multiple batches may be executed in a single transaction. Try:
BEGIN TRANSACTION
GO
CREATE VIEW TempView AS select * from tempTable
GO
COMMIT TRANSACTION
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jamie Thomson" <jamie.thomson@.int21.com> wrote in message
news:008301c367f9$f337e4b0$a301280a@.phx.gbl...
> Hello,
> I know that it is possible to put DDL statements (i.e.
> CREATE TABLE, DROP TABLE etc...) in transactions but I
> have found a peculiarity that I am trying to get around.
> I issued the following:
> BEGIN TRANSACTION
> CREATE VIEW TempView AS select * from tempTable
> COMMIT TRANSACTION
> It gave the following error message:
> Server: Msg111, level 15, State 1, Line 2
> 'CREATE VIEW' must be the first statement in a query batch
> Can anyone find a way around this using the simple T-SQL
> code above?
> Thanks in advance
> Jamie
>
> P.S. Why is there no microsoft.public.sqlserver.tsql
> newsgroup?|||Thanks Gents,
Dan's suggestion works perfectly.
i.e. :
BEGIN TRANSACTION
GO
CREATE VIEW TempView AS select * from tempTable
GO
COMMIT TRANSACTION
GO
Thanks for the advice.
Regards
Jamie
>--Original Message--
>DDL does not issue an implicit commit in SQL Server, although this may
>be the case with some other RDBMS vendors.
>-- >Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=3D800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"Jens S=FC=DFmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
>news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
>> Hello Jamie !
>> Sorry but this aint the way it goes. DDL Statements such as
>> alter,create,drop fires an Implicit commit to send the changes
>directly to
>> the database.
>> HTH, Jens S=FC=DFmeyer.
>>
>
>.
>|||> BEGIN TRANSACTION
> GO
> CREATE VIEW TempView AS select * from tempTable
> GO
> COMMIT TRANSACTION
> GO
As an aside, you couldn't do this inside the definition of a stored
procedure; you'd have to use dynamic SQL, I suppose. But that doesn't seem
to be an issue for the OP.

DBO rights

hi
As per baselining requirement, we need to drop dbo rights of the users
and assign
SELECT,INSERT,UPDATE, DELETE, EXECUTE rights on tables, SPs, views,
functions in the database.
Request you to pls revert whether dropping the dbo priviledge would affect
the functionality of database in any way.
RAHULHi Rahul,
A database always need to have an owner, and you will always have a dbo user
in a database.
Removing other users from the db_owner role should not affect the
functionality of your application however, if it is properly designed.
Jacco Schalkwijk
SQL Server MVP
"RAHUl" <anonymous@.discussions.microsoft.com> wrote in message
news:1EA6990D-BC8D-4AB4-B07E-9B617C6C9EF7@.microsoft.com...

quote:

---

> hi
> As per baselining requirement, we need to drop dbo rights of the users
> and assign
> SELECT,INSERT,UPDATE, DELETE, EXECUTE rights on tables, SPs, views,
> functions in the database.
> Request you to pls revert whether dropping the dbo priviledge would affect
> the functionality of database in any way.
> RAHUL

---