Monitoring and Recording DDL changes on SQL 2005 (NARC)

Question

Monitoring and Recording DDL changes on SQL 2005 (NARC)

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

James Greaves Ten Centuries Points: 1116 More actions · Answer 1

geno wald (4/3/2008)
This appears to be pretty much what I would want, except that I would like to be able to capture the machine name associated with the user who is making the connection. When I view the Activity Monitor in SSMS under the Management node, I can see the client computer name identified as "Host", but it is not clear how I can access that value from the trigger.
The reason this would be helpful is that there might be several people who share access to an account. Using the client computer name to distinguish where the login came from would help identify the person making those ddl changes.

Within the database and/or server trigger you may create another variable to capture the hostname and any other information you would like from the sys.sysprocesses table.

e.g.

DELCARE @hostnm VARCHAR(50)

SELECT @hostnm = hostname

FROM sys.sysprocesses

WHERE loginame = suser_sname()

and cmd <> 'awaiting command'

You would then alter the tables hosting the information to add a column for host name. Finally, you may also want to capture the "net_address" column within the sys.sysprocesses table to get the MAC address of the network card as well as the hostname.

mstjean Hall of Fame Points: 3310 More actions · Answer 2

I have a user (developer) that tried to alter a view in a db. He does not have rights to the db ("Admin") I write the 2 tables to. He DOES have rights to do what he was doing.

Granting him even write rights to the 2 audit tables seems counter intuitive; I thought the db and server triggers should execute in another security context... maybe this is my incorrect assumption. I don't want to turn on cross db ownership chaining-- that would expose me to a different set of risks when I am trying to ratchet those down.

His Error:

Msg 916, Level 14, State 1, Procedure trgMonitorChange, Line 33

The server principal "the users domain name" is not able to access the database "Admin" under the current security context

Any thoughts/recommendations?

Cursors are useful if you don't know SQL

@fracionero Right there with Babe Points: 737 More actions · Answer 3

Hi all,

Nice article.

I'm working in a similar trigger but I'm recording information in a table in a Linked server for consolidation issues, because I have several instances and several server and I need to collect all information in only a database, but when I try to the insert command, I only get a set of errors. I've checked previously the trigger in the local server and runs ok, but the fails appear when I try to execute from a remote server.

My insert command:

INSERT INTO [SRSQL1001\MANAGEMENT].[DBA_AUDIT].[dbo].[ENV_SRV_INS_DB_CHG]

([ENV_SRV_INS_DB_CHG_Server]

,[ENV_SRV_INS_DB_CHG_Name]

,[ENV_SRV_INS_DB_CHG_EventType]

,[ENV_SRV_INS_DB_CHG_PostTime]

,[ENV_SRV_INS_DB_CHG_Login]

,[ENV_SRV_INS_DB_CHG_TSQLCommand]

,[ENV_SRV_INS_DB_CHG_Completed])

values(@ServerName,@DBName,@EventType,@PostTime,@login,@TSQLCommand,@comp)

errors set

Msg 3984, Level 16, State 1, Line 1

Cannot acquire a database lock during a transaction change.

Msg 3985, Level 16, State 2, Line 1

An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.

Msg 3984, Level 16, State 1, Line 1

Cannot acquire a database lock during a transaction change.

Msg 3985, Level 16, State 1, Line 1

An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.

I have checked if I delete insert command database is created without errors.

Thanks in advance for your help

F.Racionero

Francisco Racionero
twitter: @fracionero

James Greaves Ten Centuries Points: 1116 More actions · Answer 4

mstjean (5/5/2008)
I have a user (developer) that tried to alter a view in a db. He does not have rights to the db ("Admin") I write the 2 tables to. He DOES have rights to do what he was doing.
Granting him even write rights to the 2 audit tables seems counter intuitive; I thought the db and server triggers should execute in another security context... maybe this is my incorrect assumption. I don't want to turn on cross db ownership chaining-- that would expose me to a different set of risks when I am trying to ratchet those down.
His Error:
Msg 916, Level 14, State 1, Procedure trgMonitorChange, Line 33
The server principal "the users domain name" is not able to access the database "Admin" under the current security context
Any thoughts/recommendations?

Perhaps I should have put something in my article regarding my intention for these triggers as I originally put them on production servers that had limited access. The database and server triggers will run in the security context of the individual account that is triggered by the DDL change. Therefore when the trigger goes off and wants to write the data to a separate database, that user requires write permission to the destination table.

In your case you have the following options:

1. Change the database trigger to point to an auditing table within the local database instead of a separate database and give that local auditing table write permissions to the developer.

2. Give the developer write permissions on the table that is in the auditing database. This may not be effective if you have more than one account making changes to your database.

Kenneth Younger SSC Rookie Points: 36 More actions · Answer 5

Remember, though, that these database triggers will not catch changes from using sp_rename.

L Cerniglia Mr or Mrs. 500 Points: 579 More actions · Answer 6

I love this article:

http://www.sqlservercentral.com/articles/Auditing/64176/

However, I would like to know how do you audit sp_rename?

Or to most DBAs this isn't important.

Sean Elliott (UK) SSCommitted Points: 1626 More actions · Answer 7

Look here for a full solution: http://www.sqlservercentral.com/Forums/Topic948331-149-1.aspx

L Cerniglia Mr or Mrs. 500 Points: 579 More actions · Answer 8

I am looking for this page

http://www.sqlservercentral.com/Forums/Topic948331-149-1.aspx

What happened to it?

Sean Elliott (UK) SSCommitted Points: 1626 More actions · Answer 9

You can see an alternative "article" I wrote for this at:

http://www.sqlservercentral.com/Forums/Topic947481-391-1.aspx

Lawrence-136504 SSC Eights! Points: 931 More actions · Answer 10

How are you guys handling when the server trigger kicks off when a user is in the dbcreator role but doesn't have access to insert into the table that houses all the ddl trigger events? Thks!

Sean Elliott (UK) SSCommitted Points: 1626 More actions · Answer 11

create trigger ServerAuditTrigger

on all server

-- Audit server level and database level into dbadata.dbo.ServerAudit

-- The database level is also audited into <database>.dbo.DatabaseAudit

with execute as ''server_audit''for ddl_events

jodi_burchell Valued Member Points: 60 More actions · Answer 12

@fracionero (5/6/2008)
My insert command:
INSERT INTO [SRSQL1001\MANAGEMENT].[DBA_AUDIT].[dbo].[ENV_SRV_INS_DB_CHG]
([ENV_SRV_INS_DB_CHG_Server]
,[ENV_SRV_INS_DB_CHG_Name]
,[ENV_SRV_INS_DB_CHG_EventType]
,[ENV_SRV_INS_DB_CHG_PostTime]
,[ENV_SRV_INS_DB_CHG_Login]
,[ENV_SRV_INS_DB_CHG_TSQLCommand]
,[ENV_SRV_INS_DB_CHG_Completed])
values(@ServerName,@DBName,@EventType,@PostTime,@login,@TSQLCommand,@comp)
errors set
Msg 3984, Level 16, State 1, Line 1
Cannot acquire a database lock during a transaction change.
Msg 3985, Level 16, State 2, Line 1
An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.
Msg 3984, Level 16, State 1, Line 1
Cannot acquire a database lock during a transaction change.
Msg 3985, Level 16, State 1, Line 1
An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.
I have checked if I delete insert command database is created without errors.
Thanks in advance for your help
F.Racionero

Has anyone figured out a solution to this? My trigger record is being inserted into the other server, but I sill get these error messages.

Thanks!

JMB